MySQL - How to group data by Day and Hour

I have some problem in getting the query for this sample format. My idea is select hours on that day and group it by day or select all the 7 days and group it by hours?

here is the format:

I assume you have one column of interest (dt, a DATETIME) and you COUNTing the number of rows to get the values for that table?

First, do this to see if you get the right values (but not the desired layout):

SELECT HOUR(dt), WEEKDAY(dt), COUNT(*)
      FROM tbl
      GROUP BY HOUR(dt), WEEKDAY(dt)
      ORDER BY HOUR(dt), WEEKDAY(dt);

Second, you need to "pivot" the table. However, you have particular columns.

See http://mysql.rjweb.org/doc.php/pivot for how to generate the code for pivoting.

There are still a few formatting issues...

For the headings, you need to turn "0" into "Mon", etc. Suggest you hard-code the titles.

"23" can be turned into "23:00 - 23:59" with a CONCAT() and some more code.

NULL can be turned into 0 via IFNULL().

See how far you can get. (Since this question is 3 weeks old, I don't want to put more effort into it if you have already gone away.)

Personally, I would do (and in fact have done) similar stuff using PHP instead of SQL. PHP code is longer, but less contorted.

StackExchange Database Administrators Q#116557, answer score: 6