SQL count query with join on same table

Question

Sorry in advance for this question if the solution should be obvious. I have searched but not found a solution yet.

I have an Orders table that includes 2 columns: CustID and ProductBrand. ProductBrand is the make of the item ordered, e.g. Sony, HP, Dell, etc.

I want to get the list of customers that have ordered at least 10 items with ProductBrand=Sony but less than 5 items with ProductBrand=HP.

Can I do this with something along these lines?

SELECT o1.CustID
FROM Orders o1 HAVING COUNT(o1.ProductBrand="Sony") >= 10
INNER JOIN Orders o2 ON o1.CustID = o2.CustID
HAVING COUNT(o2.ProductBrand="HP") < 5;

Answer 1

You would do this without a self-join, just aggregation:

select o.custid
from orders o
group by o.custid
having sum(case when o.ProductBrand = 'Sony' then 1 else 0 end) >= 10 and
       sum(case when o.ProductBrand = 'HP' then 1 else 0 end) < 5;

In MySQL, you can simplify this by removing the case:

select o.custid
from orders o
group by o.custid
having sum(o.ProductBrand = 'Sony') >= 10 and
       sum(o.ProductBrand = 'HP') < 5;