Reputation: 1120
Considering this simple table called events
date | reserved
-----------+---------
2019-11-13 | 1
2019-11-14 | 0
2019-11-15 | 1
Running the following query, I can retrieve the count of not reserved and reserved events:
SELECT count(*) FROM `events` GROUP BY `reserved`
1
2
However, if all events are reserved I will get a single row with a single column:
3
What is the simplest way to get instead:
0
3
Upvotes: 1
Views: 1305
Reputation: 147146
You can LEFT JOIN
to a derived table of possible reserved
values:
SELECT r.reserved, COUNT(e.reserved)
FROM (SELECT 0 AS reserved UNION ALL SELECT 1) r
LEFT JOIN events e ON e.reserved = r.reserved
GROUP BY r.reserved
Upvotes: 3
Reputation: 222402
If you are content with having both results on a single record, you can do conditional aggregation:
select
sum(reserved = 0) not_reserved,
sum(reserved = 1) reserved
from mytable
This seems to me like a more sensible option, since the information it returns (in columns) is easier to understand that when provided as rows (how do you know which record correspond to the count of not reserved, especially if you don't order the results?).
Upvotes: 2