Counting multiple rows in MySQL in one query

Question

I currently have a table which stores a load of statistics such as views, downloads, purchases etc. for a multiple number of items. To get a single operation count on each item I can use the following query:

SELECT *, COUNT(*)
FROM stats
WHERE operation = 'view'
GROUP BY item_id

This gives me all the items and a count of their views. I can then change 'view' to 'purchase' or 'download' for the other variables. However this means three separate calls to the database.

Is it possible to get all three in one go?

Answer 1

SELECT item_id, operation, COUNT(*) 
FROM stats 
WHERE operation IN ('view','purchase','download') 
GROUP BY item_id, operation

Will return a table with one line per item_id and operation, containing three columns: the item_id, the operation type, and the number of rows with that item_id.

1 view 3
1 purchase 5
2 download 7
3 download 1

You can omit the WHERE if you want all item_id's, and you can order in COUNT(*) to get the most popular or something. Depends what you are looking for or how you are using the data.

If you want the columns next to eachother, use an IF:

SELECT s1.item_id, SUM( IF( s1.operation = 'view', 1, 0 ) ) views, SUM( IF( s1.operation = 'download', 1, 0 ) ) downloads, SUM( IF( s1.operation = 'purchase', 1, 0 ) ) purchases
FROM stats s1
GROUP BY s1.item_id

item_id | views | downloads | purchases
1 | 3 | 0 | 5
2 | 0 | 7 | 0
3 | 0 | 1 | 0