SQL counter query with join in one table

Question

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

I have an order table that includes 2 columns: CustID and ProductBrand. ProductBrand is the brand of the ordered product, for example. Sony, HP, Dell, etc.

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

Can I do this with something in this direction?

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 independent unification, 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 case :

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

Answer 2

Script setup

 CREATE TABLE ORDERS (cust INT, brand VARCHAR(100)) INSERT INTO ORDERS VALUES (1, 'Sony') INSERT INTO ORDERS VALUES (1, 'Sony') INSERT INTO ORDERS VALUES (1, 'Sony') INSERT INTO ORDERS VALUES (1, 'Hp') INSERT INTO ORDERS VALUES (1, 'Hp') INSERT INTO ORDERS VALUES (2, 'Sony') INSERT INTO ORDERS VALUES (2, 'Sony') INSERT INTO ORDERS VALUES (2, 'Hp') INSERT INTO ORDERS VALUES (2, 'Hp') INSERT INTO ORDERS VALUES (2, 'Hp') INSERT INTO ORDERS VALUES (2, 'Hp') INSERT INTO ORDERS VALUES (3, 'Sony') INSERT INTO ORDERS VALUES (3, 'Sony') INSERT INTO ORDERS VALUES (3, 'Sony') INSERT INTO ORDERS VALUES (3, 'Hp') INSERT INTO ORDERS VALUES (3, 'Hp') 

Request

Method 1

 select sony.custId from (select cust as custId, Brand, count(*) as count from orders where Brand = 'Sony' group by cust, brand) Sony inner join (select cust as custId, Brand, count(*) as count from orders where Brand = 'Hp' group by cust, brand) Hp on sony.custId = hp.CustId Where sony.count > 2 and hp.count < 5 

It is good for you to understand the attitude. Here is another way

Method 2

 select cust from orders group by cust, brand having count(case brand when 'Sony' then 'x' end) > 2 and count(case brand when 'Hp' then 'x' end) < 5 

The time is approximately the same - of course, it will vary depending on the amount of data, indices, etc. Use the Sql script to verify the execution plan. It seems that more is happening in the first method. But this does not mean that when used in production it will be much worse.