winck
winck

Reputation: 1120

How can I include a non existing value in GROUP BY with COUNT(*)?

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

Answers (2)

Nick
Nick

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

Demo on dbfiddle

Upvotes: 3

GMB
GMB

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

Related Questions