CODEWITHSUNDEEP
oracle-database
user3178457
user3178457

Reputation: 1

how to exclude group with same value in it?

I have a scenario where an id is tied to more than one value/code and I would like to exclude get all id which has multiple types and one of it should be type 11 but not all of the type should be 11.

create table t (t_id number, t_type number);

insert into t(T_ID, T_TYPE) values (1147, 1);
insert into t(T_ID, T_TYPE) values (1147, 35);
insert into t(T_ID, T_TYPE) values (1147, 11);
insert into t(T_ID, T_TYPE) values (1148, 11);
insert into t(T_ID, T_TYPE) values (1148, 11);
insert into t(T_ID, T_TYPE) values (1149, 1);
insert into t(T_ID, T_TYPE) values (1149, 11);
insert into t(T_ID, T_TYPE) values (1150, 35);
insert into t(T_ID, T_TYPE) values (1151, 11);
insert into t(T_ID, T_TYPE) values (1152, 27);
insert into t(T_ID, T_TYPE) values (1152, 11);
insert into t(t_id, t_type) values (1153, 1);
insert into t(t_id, t_type) values (1153, 27);

commit;

Output:

select * from t;

T_ID  T_TYPE
====  ======
1147       1
1147      35
1147      11
1148      11
1148      11
1149       1
1149      11
1150      35
1151      11
1152      27
1152      11
1153       1
1153      27

Here I would like to exclude the t_id 1148 which has multiple occurrence but is tied to type 11 all the times.

WITH ty AS(
   SELECT t_id
     FROM t
 GROUP BY t_id
   HAVING COUNT(1) > 1
)
SELECT t.t_id
  FROM t, ty
 WHERE ty.t_id = t.t_id
   AND t.t_type = 11
 GROUP 
    BY t.t_id;


      T_ID
----------
      1148
      1149
      1152
      1147

Upvotes: 0

Views: 54

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think this does what you want:

select t_id
from t
group by t_id
having sum(case when t_id = 11 then 1 else 0 end) > 0 and
       sum(case when t_id <> 11 then 1 else 0 end) > 0;

The having clause ensures that it has at least one row that is 11 and at least one row that is not 11.

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

If you count the distinct occurences of t_type like below, it will exclude t_id=1148.

select t_id
  from t
 where t_id in(select t_id from t where t_type = 11)
 group 
    by t_id
having count(distinct t_type) > 1;

Upvotes: 0

stovroz
stovroz

Reputation: 7065

select t_id from t where t_type = 11 
intersect 
select t_id from t where t_type <> 11

Upvotes: 0

Related Questions