SQL not returning a value if no row exist for time queried

I’m writing this SQL query which returns the number of records created in an hour in last 24 hours. I’m getting the result for only those hours that have a non zero value. If no records were created, it doesn’t return anything at all.

Here’s my query:

SELECT HOUR(timeStamp) as hour, COUNT(*) as count 
FROM `events` 
WHERE timeStamp > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(timeStamp)
ORDER BY HOUR(timeStamp)

The output of current Query:

+-----------------+----------+
| hour            | count    |
+-----------------+----------+
|              14 |        6 |
|              15 |        5 |
+-----------------+----------+

But i’m expecting 0 for hours in which no records were created. Where am I going wrong?

Answer

One solution is to generate a table of numbers from 0 to 23 and left join it with your original table.

Here is a query that uses a recursive query to generate the list of hours (if you are running MySQL, this requires version 8.0):

with hours as (
    select 0 hr
    union all select hr + 1 where h < 23
)
select h.hr, count(e.eventID) as cnt
from hours h
left join events e 
    on e.timestamp > now() - interval 1 day
    and hour(e.timestamp) = h.hr
group by h.hr

If your RDBMS does not support recursive CTEs, then one option is to use an explicit derived table:

select h.hr, count(e.eventID) as cnt
from (
    select 0 hr union all select 1 union all select 2 ... union all select 23
) h
left join events e 
    on e.timestamp > now() - interval 1 day
        and hour(e.timestamp) = h.hr
group by h.hr