MySQL query to generate a punch card dataviz

I have a database with a chat history that has a datime field, author of the messsage and the message.

I wan´t to make a query that returns a table that, correlates the weekday with hours of day, counting how many messages was sent, using the same format that github uses to generate the punch card graph:

[day, hour, number of messages]

[
[0,0,23],
[0,1,20],
[0,2,56],
[]...]

I tried this SQL instruction, but I don't think is working.

select 
    dayofweek(date_time) as DAY, 
    hour(date_time) as `HOUR`, 
    sum(dayofweek(date_time)) as Msgs 
from chat
group by DAY(date_time)
order by `DAY`

The return I get is:

[[1,1,4734]
[1,20,4503]
[1,11,6510]
[1,0,7058]
[2,0,6518]
[2,8,3913]
[2,9,3885]
[2,0,2305]
[2,9,4471]
[2,0,3703]
[3,0,3315]
[3,0,9600]
[3,0,3910]
[3,9,4956]
[3,0,2692]
[4,0,5225]
[4,10,4815]
[4,0,7667]
[4,11,5249]
[4,0,5121]
[5,8,3148]
[5,10,4947]
[5,0,4109]
[5,0,4775]
[6,0,4970]
[6,0,4342]
[6,12,4488]
[6,0,7191]
[7,0,3790]
[7,9,9294]
[7,9,4749]]

I don't know what is happening, but the return should have 168 lines, 7 * 24, what I'm missing here?

Thanks!

Solution

The problem is you are grouping by day only, not day and hour. So instead of

select 
    dayofweek(date_time) as DAY, 
    hour(date_time) as `HOUR`, 
    sum(dayofweek(date_time)) as Msgs 
from chat
group by DAY(date_time)
order by `DAY`

select 
    dayofweek(date_time) as DAY, 
    hour(date_time) as `HOUR`, 
    sum(dayofweek(date_time)) as Msgs 
from chat
group by dayofweek(date_time), hour(date_time)
order by `DAY`,HOUR