CODEWITHSUNDEEP
sqlfunctionjoinsumhaving
user1864024
user1864024

Reputation: 11

SQL Sum() function with a condition

How does this work in SQL sum(o.product_name='Bread') > 0, e.g if the orders table (o) has product_name column such that the entry is Bread then will the condition o.product_name='Bread' return 1 to the sum function and then the sum function will add all these ones ?

The Question is : There are 2 tables customers, orders. Find the customers who bought bread and milk but not eggs

Select  c.customer_id , c.customer_name
        FROM customers as c 
        left join
           orders as o
           on c.customer_id = o.customer_id
        group by c.customer_id , c.customer_name
        having 
        sum(o.product_name='Bread') > 0 
        and sum(o.product_name='Milk') > 0
        and sum(o.product_name='Eggs') = 0
        order by c.customer_name

Upvotes: -5

Views: 90

Answers (1)

Chander Kumar
Chander Kumar

Reputation: 14

As for your question, you need only those customers who bought both bread and milk but not eggs. I simply filters for the customers who bought both and then again do filtration to remove those who also bought eggs.

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.product_name IN ('Eggs', 'Milk')
GROUP BY c.customer_id
HAVING COUNT(DISTINCT o.product_name) = 2
   AND COUNT(CASE WHEN o.product_name = 'Bread' THEN 1 END) = 0;

Upvotes: -1

Related Questions