Counting multiple rows in MySQL in one query
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 data开发者_开发技巧base.
Is it possible to get all three in one go?
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
精彩评论