Dual conditional count select while grouping

Question

I have a table with the following relevant fields:

entity_id | grouping_id | type_id
---------------------------------
1         | 1           | 1
2         | 1           | 1
3         | 1           | 2
4         | 2           | 1
5         | 2           | 2

I want to select all grouping_id values where there are X number of type n and Y number of type m. Example of what I initially thought would work:

select grouping_id from t_entities
group by grouping_id, type_id
having
    count(case type_id when 1 then 1 else null end) = 2
and
    count(case type_id when 2 then 1 else null end) = 1

Would theoretically return all grouping_id values that have 2 of type 1 and 1 of type 2, but because the clause is grouping by type_id as well, I get nothing back because each group exists independently, but if I drop the group by type_id then I get aggregation errors. Is there a way to do this as a raw query without using temp tables?

Answer 1

You are on the right track with your query, however instead of grouping by type_id you should just be grouping by the grouping_id. By grouping by the type_id you won't ever get something that has two different values, so your and clause in the having won't work. You should be able to execute the following to return grouping_id =1 as the result:

select grouping_id 
from t_entities
group by grouping_id
having count(case type_id when 1 then 1 else null end) = 2
  and count(case type_id when 2 then 1 else null end) = 1;

See SQL Fiddle with Demo