CODEWITHSUNDEEP
sqloracle-databaseselectcountgroup-by
Ivan Gerasimenko
Ivan Gerasimenko

Reputation: 2428

GROUP BY Create group if at least one value in group meets condition

How to create group if at least one value in group meets condition?

Here is an example of DB table test:

| ID | TYPE | COLOR |
|====|======|=======|
|  1 |    1 |     R |
|  2 |    1 |     B |
|  3 |    1 |     G |
|  4 |    2 |     B |
|  5 |    2 |     G |
|  6 |    3 |     G |

I need to select all TYPE values that have more than one row, and at least one of COLORs is G for that TYPE.

So pseudo-select would look like this:

  select TYPE 
    from test
group by TYPE
  having count(*) > 1
     and count(COLOR = 'G') > 0

Upvotes: 8

Views: 7396

Answers (3)

user5683823
user5683823

Reputation:

With the OP's modified requirement:

select   type
from     test
group by type
having   count(*) > 1 and count(case when color = 'G' then 0 end) > 0
;

Upvotes: 8

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a inner join on th count(color)

 select t1.type 
 from test  t1
 inner join (
     select type, count(color)
     from test
     where  type in (select  type from test where color='G' )
     group by type
 ) t2 on t1.type = t2.type
 group by t1.type 
 having count(*) > 1

or in a simplest way 

   select t1.type 
   from test  t1
   inner join  test t2 on t1.type = t2.type and t2.color = 'G'
   group by t1.type 
   having count(*) > 1

Upvotes: 1

Mureinik
Mureinik

Reputation: 310993

count only counts non-null values. A neat trick to wrap both required conditions into one is to count the distinct number of a case expression that returns something for G and something else for any other value:

SELECT   type
FROM     test
GROUP BY type
HAVING   COUNT(DISTINCT CASE color WHEN 'G' THEN 1 ELSE 2 END) = 2

Upvotes: 0

Related Questions