CODEWITHSUNDEEP
sqlsql-servergroup-by
CodeBob
CodeBob

Reputation: 119

SSMS SQL Identify purchases who only buy one specific item

I want to find customers who have only bought fruit in their purchase. My data looks like this:

ID         purchase_date    product
22212      2021-06-03       Fruit
111999     2021-06-03       Fruit
111999     2021-06-03       Chocolate
56727      2019-05-03       Bread
30726      2019-05-03       Fruit
30726      2019-05-03       Chocolate
53899      2019-05-03       Fruit

I want this:

  ID         purchase_date
  22212      2021-06-03
  53899      2019-05-03

Thinking I need a where clause, grouped by ID and Purchase_date?

Upvotes: 1

Views: 116

Answers (3)

Arpit Chinmay
Arpit Chinmay

Reputation: 333

You can use correlated queries as well.

SELECT DISTINCT T1.ID, T1.purchase_date
FROM <TableName> T1
WHERE T1.product = 'Fruit'
    AND NOT EXISTS (    SELECT 1 
                        FROM <TableName> T2 
                        WHERE Product <> 'Fruit' 
                        AND T1.ID = T2.ID 
                   )

Upvotes: 0

Aman
Aman

Reputation: 99

Select Id, purchase_date 
From your_table_name 
Where lower(product) = 'fruit'

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to use Having with condition aggregate function to write the logic.

  • Buy fruit (COUNT with fruit count will be greater than 1 )
  • Din't buy any product without Fruit

Query 1:

SELECT ID,purchase_date    
FROM T
GROUP BY  ID,purchase_date    
HAVING 
    COUNT(CASE WHEN product='Fruit' THEN 1 END) > 0 
AND
    COUNT(CASE WHEN product<>'Fruit' THEN 1 END) = 0

Results:

|    ID | purchase_date |
|-------|---------------|
| 53899 |    2019-05-03 |
| 22212 |    2021-06-03 |

Upvotes: 3

Related Questions