Home > Software engineering >  SQL - Returning all transactions based on one value
SQL - Returning all transactions based on one value

Time:04-29

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.

  •  Tags:  
  • sql
  • Related