Filter results within groups sql

In the table below, I want to know how many customers ordered lunch without a coffee. The result would be 1, for sale ID 300, because two lunches were ordered but only one coffee.

It’s been 8 years since I last used SQL! How do I say “group the records by sale ID and for each group, drop groups where there is no lunch or COUNT(coffee) < COUNT(lunch)"?

Solution 1:

here is one way:

select count(*) from (
   select saleID 
   from tablename
   group by saleID
   having sum(case when product ='coffee' then 1 else 0 end) = 0 
   and sum(case when product ='lunch' then 1 else 0 end) = 1
) t

Solution 2:

You can do it with aggregation and the conditions in the HAVING clause.

This query:

SELECT sale_id
FROM tablename
GROUP BY sale_id
HAVING SUM(product = 'lunch') > SUM(product = 'coffee'); 

returns all the sale_ids that you want.

This query:

SELECT DISTINCT COUNT(*) OVER () counter
FROM tablename
GROUP BY sale_id
HAVING SUM(product = 'lunch') > SUM(product = 'coffee');

returns the number of sale_ids that you want.

See the demo.

Solution 3:

select count(*) from (
  --in this subquery calculate counts and ignore items that haven't any lunch
   select 
    saleID, sum(case when product ='coffee' then 1 else 0 end) as coffee,
    sum(case when product ='lunch' then 1 else 0 end)  lunch
   from tablename
   group by saleID
   having sum(case when product ='lunch' then 1 else 0 end) >= 1  --Here we are ignoring all items haven't any lunch
) t
where lunch > coffee -- we check second condition be ok