table stores has two columns like store_id and products. Write a SQL query to find store_ids which sell only shampoo and biscuit
Store should sell only two products and that too only shampoo and biscuit
For example
Output:
my query: -
SELECT *
FROM (SELECT store_id AS gp_str
FROM (SELECT store_id,
COUNT(product) AS prd_cnt
FROM stores
GROUP BY store_id) x
WHERE x.prd_cnt = 2) y
LEFT JOIN stores ON y.gp_str = stores.store_id;
My query gave me result of stores which sell only two products but I wanted stores which sell two products which are shampoo and biscuit only.
SELECT store_id
FROM stores
GROUP BY store_id
HAVING COUNT(DISTINCT product) = 2
AND COUNT(*) = SUM(CASE WHEN product IN ('shampoo','biscuit') THEN 1 ELSE 0 END);