Using MySQL to count all rows in which rows have col A has value X and value Y

I want to count the number of times pairs of products have been purchased by customers. For a given transaction there's a unique transaction_id and a row in the database for each product in the basket.

id product_id    product_name    transaction_id
1  123           Eggs            8
2  12            Milk            8
3  124           Beer            8
4  123           Eggs            9
5  12            Milk            9

I can easily count the number of transactions in which a product was purchased using a query like this.

SELECT 
COUNT(transaction_id), 
product_id, 
product_name 
FROM crm_purchases 
开发者_如何学GoWHERE 
product_id = '123' 

This tells me that 2 people bought Eggs. I want to create a similar query which counts how many transactions included eggs and milk by counting all transaction_ids that exist when rows contain both 12 and 123 in the product_id column.

Any ideas or pointers on how I can achieve this, please? It's had me stumped for hours!

---

You need to join the table to itself, so that you have a result set containing every combination of two rows (for a given transaction_id) and then select out the combinations you want e.g.

SELECT COUNT(*)
FROM crm_purchases cp1
JOIN crm_purchases cp2 USING (transaction_id)
WHERE cp1.product_id = 123
AND cp2.product_id = 12;

I encourage you to try the query without the COUNT or WHERE (on a small dataset!) so that you can see what it's actually doing.

---

Do you mean like SELECT COUNT(transaction_id) AS myCount and then myCount contain the actual count?

---

This will return one row per pair purchased

SELECT COUNT(*), product_id, transaction_id
FROM crm_purchases
WHERE product_id IN ('123', '12')
GROUP BY transaction_id
HAVING COUNT(*) = 2

The HAVING clause is kind of like a WHERE for GROUP BY values.