Using a large table of retail transactions I am trying to review customer purchases where they bought a specific product category.
Table example below, customers 1 and 3 bought an item of citrus so I want to get ALL of the items from that transaction, not just the citrus items. I will then group the customer purchases together.
Transaction | Product | Category |
---|---|---|
Customer 1 | Fish | Protein |
Customer 1 | Cheese | Dairy |
Customer 1 | Oranges | Citrus |
Customer 1 | Beer | Alcohol |
Customer 2 | Eggs | Protein |
Customer 2 | Beer | Alcohol |
Customer 3 | Cheese | Dairy |
Customer 3 | Fish | Protein |
Customer 3 | Lemons | Citrus |
Have used WHERE EXISTS but this isn't returning the correct products.
SELECT
Transaction,
Product,
Category
WHERE EXISTS
(SELECT *
FROM table
WHERE Category = 'Citrus')
Desired Output
Transaction | Products Bought |
---|---|
Customer 1 | 4 |
Customer 3 | 3 |
CodePudding user response:
You can achieve your desired output using the following query
SELECT
Transaction,
COUNT(Product) AS Products_Bought
FROM TABLE
WHERE Transaction IN (SELECT Transaction
FROM TABLE
WHERE Category = 'Citrus')
GROUP BY Transaction;
CodePudding user response:
There are likely a lof of possibilites to receive your desired result. You could create a subselect first to find the matching rows (meaning those having 'Citrus' as category) and after this count and group them:
SELECT transaction, COUNT(*) AS ProductsBought
FROM table
WHERE transaction IN
(SELECT transaction FROM table WHERE category = 'Citrus')
GROUP BY transaction
ORDER BY transaction;
You can see a working example here: db<>fiddle
Just a note to the linked example: I renamed both the table and the column since you can't execute the queries there otherwise. I assume this is not a problem in your DB, but if so, you could rename the column if possible or escape it in the query.