MariaDB column group by hour, row group by title

Question

Here is a table https://paiza.io/projects/Q1VykBxszKlxqmiyqknWFw?language=mysql

title|record_datetime
--------------------------
fgame 2018-07-09 11:41:41
fgame 2018-07-09 11:41:25
fgame 2018-07-09 12:41:41
source 2018-07-09 10:40:18
source 2018-07-09 10:40:33
source 2018-07-09 10:41:10
setup 2018-07-24 09:49:11
setup 2018-07-24 09:49:13

I want the result like this sample

15 columns(title+ 13hours+ sum) and 23 rows(22 different titles + sum)

hours from 8 to 20 group by record_datetime

How can i do to make this? I have tried

SELECT * FROM (SELECT title, count(*)as h8 FROM click_log where HOUR(record_datetime) = 8 GROUP BY title) as t8
Right join (SELECT title, count(*)as h9 FROM click_log where HOUR(record_datetime) = 9 GROUP BY title) as t9 on t8.title = t9.title
Right join (SELECT title, count(*)as h10 FROM click_log where HOUR(record_datetime) = 10 GROUP BY title) as t10 on t9.title = t10.title
Right join (SELECT title, count(*)as h11 FROM click_log where HOUR(record_datetime) = 11 GROUP BY title) as t11 on t10.title = t11.title
Right join (SELECT title, count(*)as h12 FROM click_log where HOUR(record_datetime) = 12 GROUP BY title) as t12 on t11.title = t12.title
Right join (SELECT title, count(*)as h13 FROM click_log where HOUR(record_datetime) = 13 GROUP BY title) as t13 on t12.title = t13.title
Right join (SELECT title, count(*)as h14 FROM click_log where HOUR(record_datetime) = 14 GROUP BY title) as t14 on t13.title = t14.title
Right join (SELECT title, count(*)as h15 FROM click_log where HOUR(record_datetime) = 15 GROUP BY title) as t15 on t14.title = t15.title
Right join (SELECT title, count(*)as h16 FROM click_log where HOUR(record_datetime) = 16 GROUP BY title) as t16 on t15.title = t16.title
Right join (SELECT title, count(*)as h17 FROM click_log where HOUR(record_datetime) = 17 GROUP BY title) as t17 on t16.title = t17.title
Right join (SELECT title, count(*)as h18 FROM click_log where HOUR(record_datetime) = 18 GROUP BY title) as t18 on t17.title = t18.title
Right join (SELECT title, count(*)as h19 FROM click_log where HOUR(record_datetime) = 19 GROUP BY title) as t19 on t18.title = t19.title
Right join (SELECT title, count(*)as h20 FROM click_log where HOUR(record_datetime) = 20 GROUP BY title) as t20 on t19.title = t20.title
Right join (SELECT title, count(*)as allh FROM click_log where 1=1 GROUP BY title) as tallh on t20.title = tallh.title
UNION 
SELECT * FROM (SELECT title, count(*)as h8 FROM click_log where HOUR(record_datetime) = 8 GROUP BY title) as t8
LEFT join (SELECT title, count(*)as h9 FROM click_log where HOUR(record_datetime) = 9 GROUP BY title) as t9 on t8.title = t9.title
LEFT join (SELECT title, count(*)as h10 FROM click_log where HOUR(record_datetime) = 10 GROUP BY title) as t10 on t9.title = t10.title
LEFT join (SELECT title, count(*)as h11 FROM click_log where HOUR(record_datetime) = 11 GROUP BY title) as t11 on t10.title = t11.title
LEFT join (SELECT title, count(*)as h12 FROM click_log where HOUR(record_datetime) = 12 GROUP BY title) as t12 on t11.title = t12.title
LEFT join (SELECT title, count(*)as h13 FROM click_log where HOUR(record_datetime) = 13 GROUP BY title) as t13 on t12.title = t13.title
LEFT join (SELECT title, count(*)as h14 FROM click_log where HOUR(record_datetime) = 14 GROUP BY title) as t14 on t13.title = t14.title
LEFT join (SELECT title, count(*)as h15 FROM click_log where HOUR(record_datetime) = 15 GROUP BY title) as t15 on t14.title = t15.title
LEFT join (SELECT title, count(*)as h16 FROM click_log where HOUR(record_datetime) = 16 GROUP BY title) as t16 on t15.title = t16.title
LEFT join (SELECT title, count(*)as h17 FROM click_log where HOUR(record_datetime) = 17 GROUP BY title) as t17 on t16.title = t17.title
LEFT join (SELECT title, count(*)as h18 FROM click_log where HOUR(record_datetime) = 18 GROUP BY title) as t18 on t17.title = t18.title
LEFT join (SELECT title, count(*)as h19 FROM click_log where HOUR(record_datetime) = 19 GROUP BY title) as t19 on t18.title = t19.title
LEFT join (SELECT title, count(*)as h20 FROM click_log where HOUR(record_datetime) = 20 GROUP BY title) as t20 on t19.title = t20.title
LEFT join (SELECT title, count(*)as allh FROM click_log where 1=1 GROUP BY title) as tallh on t20.title = tallh.title

For Pivoting in MariaDB, see See https://stackoverflow.com/a/56670844/1766831 — Rick James, Nov 01 '21 at 02:28

score 2 · Accepted Answer · answered Jun 22 '20 at 10:47

2

Just use with rollup and conditional aggregation:

SELECT COALESCE(title, 'sum'),
       SUM( HOUR(record_datetime) = 8 ) AS hour_08,
       SUM( HOUR(record_datetime) = 9 ) AS hour_09,
       SUM( HOUR(record_datetime) = 10 ) AS hour_10,
       . . .,
       COUNT(*) as total
FROM your_table
GROUP BY title WITH ROLLUP;

Note that I gave the columns names that do not need to be escaped. You are free to use names like 08:00, but I recommend names that don't need to be escaped.

answered Jun 22 '20 at 10:47

Gordon Linoff

1,242,037
58
646
786

Amazing! Thank you. – AllenBooTung Jun 23 '20 at 01:37
1

@AllenBooTung . . . I thought `ROLLUP` would put the summation row last, If not, you can use `order by (title is null) desc`. – Gordon Linoff Jun 23 '20 at 02:17
1

http://mysql.rjweb.org/doc.php/pivot provides a generic procedure for building that `SELECT`. – Rick James Jun 24 '20 at 17:23

mkRabbani · Answer 2 · 2020-06-23T04:51:35.627

If I understand your requirement correct, this below query should help you getting your required output-

Demo Here

SELECT title,
SUM(CASE WHEN HOUR(record_datetime) = 6 THEN 1 ELSE 0 END) AS '06.00',
SUM(CASE WHEN HOUR(record_datetime) = 7 THEN 1 ELSE 0 END) AS '07.00',
SUM(CASE WHEN HOUR(record_datetime) = 8 THEN 1 ELSE 0 END) AS '08.00',
SUM(1) total
FROM your_table
GROUP BY title

UNION ALL

SELECT 'sum', 
SUM(CASE WHEN HOUR(record_datetime) = 6 THEN 1 ELSE 0 END) AS '06.00',
SUM(CASE WHEN HOUR(record_datetime) = 7 THEN 1 ELSE 0 END) AS '07.00',
SUM(CASE WHEN HOUR(record_datetime) = 8 THEN 1 ELSE 0 END) AS '08.00',
SUM(1) total
FROM your_table

I just added Hour 6,7 & 8 as sample. You can add other HOURS to the query as per requirement.

Output of second query can be also generated at the Front end of your application. But if this is a requirement of generating this part through query, you can use the above given logic.

I edit the DATEPART to HOUR to fit MariaDB, it works. Thank you. — AllenBooTung, Jun 23 '20 at 01:36

MariaDB column group by hour, row group by title

2 Answers2