MySQL Query for obtaining count per hour

Question

I need to obtain a count of how many actions occur on an hourly basis.

My database keeps a log by timestamp of the actions.

I understand that I could do a

SELECT table.time COUNT(table.time) from table t group by t.time

However, there are periods of time where no actions take place. For example if I have 10 actions during 8:00AM, no actions during 9:00AM and 4 actions during 10:00AM,

That query would return:

8:00    10
10:00   4

Skipping 9:00AM because it has no entries.

How can I make a query that will take into account 0-count entries.

I also need to make the same query for entries by days of the week, but I assume that by answering the first question I can easily handle the other.

Thanks in advance!

Answer 1

you can solve this by creating a table that will contain 24 values for hours (00:00, 01:00 etc) and perform a left (or right) join with it and your table allowing nulls so you will have all 24 rows even if your table contains 0 rows at all, then group by should work fine.

Dont forget to truncate everything but hour from your table when you perform join so result of func you call & perform join on can be equal to value of this help table.

you can use following query to do the job after populating testtime table with 24 test_time values

select test_time,sum(sign(coalesce(idFromYourTable,0))) as count from testtime 
left join yourTable on test_time=hour(yourTableTime) 
group by test_time

This will provide 0 as count if there are no values matching row from test table, while having count(*) will provide 24 rows with 1s instead of 0s even if your table is empty, also if there is just 1 row in your table it is impossible to distinguish the difference between 0 rows cause results will look the same for following 2 different rows

23 NULL
23 1

cause will both provide same result row count equal to 1 , while sum technique treats this rows differently