CODEWITHSUNDEEP
sqljoin
Point
Point

Reputation: 51

SQL count query with join on same table

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;

Upvotes: 5

Views: 247

Answers (2)

T.S.
T.S.

Reputation: 19330

Setup fiddle

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')

Query

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

This is good for you to understand relation. 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 about the same - sure it will vary based on amount of data, indexes, etc. Use Sql fiddle to check execution plan. Looks like in first method more things are going on. But doesn't mean that when you use in production, it will be much worse.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269573

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;

Upvotes: 4

Related Questions