Counting multiple rows in MySQL in a single query

Question

Currently, I have a table that stores statistics, such as views, downloads, purchases, etc. for multiple items. To get one operation instance for each item, I can use the following query:

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

It gives me all the objects and counting their views. Then I can change the “view” to “purchase” or “download” for other variables. However, this means three separate calls to the database.

Is it possible for all three at a time?

Answer 1

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

​​item_id , : item_id, item_id.

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

WHERE, item_id, COUNT (*), - . , .

eachother, 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