SQL - Returning all transactions based on one value-CodePudding

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.