How show registers per hour - Mysql

Question

I'm working in MySql, I need to get a MySQL query with all the records in a table grouped by hour, but I also need the times when there are no records.

For example, I have a users table, I want to get registered users by the hour and to give me something like this:

1:00 10
2:00 15
3:00 3
4:00 0
5:00 0
6:00 1
.
.
.
24:00 12

Does anyone know how to do it?

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 03 '17 at 21:01

score 0 · Answer 1 · answered May 03 '17 at 21:04

You can create an hours table and add 24 records into it, one for each hour.

Once done, you can LEFT JOIN that, with users table and use GROUP BY with COUNT by applying HOUR function to extract the hour from date (assuming the column in question is a datetime column), e.g.

SELECT h.hour, COUNT(u.*)
FROM hours h LEFT JOIN users u ON h.hour = HOUR(u.registered)
GROUP BY h.hour;

How show registers per hour - Mysql

1 Answers1