I have a table where I store some items. These items can be of four types (1,2,3,4) and each item can be open (status=1 or 2) or closed (status=3).
I want to build a dataset where I have for each type the count of how many items are in status 3 and the total amount of items for that type. I have no problems to extract these informations separately (SELECT count(id), type FROM table WHERE status=3 GROUP BY type and SELECT count(id), type FROM table GROUP BY type) but I would like to have them side by side. I was wondering to join the two tables by type but maybe there is an easier way to do it?
Note: running mysql 8
| id | type | status |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 1 |
| 6 | 3 | 1 |
| 7 | 4 | 3 |
| 8 | 4 | 3 |
| Type | closed | total |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 0 | 2 |
| 3 | 0 | 1 |
| 4 | 1 | 1 |
CodePudding user response：
We can try to use the condition aggregate function to add conditions to the aggregate function.
SELECT Type,
SUM(distinct status = 3) closed,
COUNT(*) total
FROM T
GROUP BY Type