Mysql , SUM column and GROUP BY HOUR

Question

Situation (simplified):

tableA

id |   date               | val
------------------------------
0    2018-02-19 00:01:00  | 10
1    2018-02-19 00:02:00  | 10
2    2018-02-19 00:03:00  | 10
..   2018-02-19 23:59:00  | 10

I need to do a query that return for each hour the SUM of the column val.

This is the query

SELECT `AllHours`.`hour` , COALESCE(SUM(`A`.`val`),0) AS `A`.`total`
FROM `tableA` AS `A`

RIGHT JOIN (
    SELECT  0 AS `hour`
    UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3
         UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6
         UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
  )      AS `AllHours` ON HOUR(`A`.`date`) = `AllHours`.`hour`


WHERE `A`.`date` BETWEEN '2018-02-19 00:00:00' AND '2018-02-19 23:59:59' OR `A`.`date` IS NULL

GROUP BY `AllHours`.`hour`
ORDER BY `AllHours`.`hour`

Result

The query works, but hour 11 is missing.

Important note I need all the hour, also if there aren't data. Otherwise i wouldn't have added the RIGHT JOIN and it would have been enough a GROUP BY HOUR(date).

Seriously consider handling issues of data display in application code. — Strawberry, Feb 19 '18 at 13:55
Whence is the column `ptot` which appears in your query but not in your data? — Tim Biegeleisen, Feb 19 '18 at 13:55
Can you explain what this `IF` expression is doing: `IF(hour < 10 , '0' , '')` ? By the way, you are grouping by the hour (sensible), while selecting something different. AFAIK the 11th hour should not be disappearing, as your calendar table should be bringing that in. — Tim Biegeleisen, Feb 19 '18 at 13:58
You need to make this reproducible. Consider setting up a demo on this site: http://www.rextester.com — Tim Biegeleisen, Feb 19 '18 at 14:03
@TimBiegeleisen This is the problem. For my test i used a date without data, and the result is always the same (without the 11 hour) — Whiteboard, Feb 19 '18 at 14:07
Switch from `WHERE` to `ON`! (And perhaps remove the IS NULL part.) — jarlh, Feb 19 '18 at 14:20
`ON 'D'.'date' BETWEEN '2018-01-23 00:00:00' AND '2018-01-23 23:59:59'` ? — Whiteboard, Feb 19 '18 at 14:24
Try moving the coalesce inside the sum bracket in the first line. I.e. `SUM(COALESCE('A'.'val',0))` — Joe, Feb 19 '18 at 14:29
Think you also need a coalesce in your select, group by and order by `'AllHours'.'hour'`. so that they all `coalesce('AllHours'.'hour', CURDATE())`. I'm not a MySQL user, so the replacement, might be something like the date, plus the hour from the right join — Joe, Feb 19 '18 at 14:37
Out of curiosity if you eliminate the where clause do you get 11 hour in the results? if so then does moving the where clause to the join itself as suggested by Whiteboard or jarlh work (you no longer need the null check if you do this) and... `COALESCE(SUM(`A`.`val`),0) AS `A`.`total`` the sum needs to occur on the outside. SUM(COALESCE(`A`.`val`,0)) AS `A`.`total` or you're summing null — xQbert, Feb 19 '18 at 14:40

score 3 · Accepted Answer · edited Feb 21 '18 at 13:31

Consider using a numbers table - see links below. This will give you the desired output for now:

Testdata:

CREATE TABLE T   (`id` int, `dt` datetime, `val` int);        
INSERT INTO T    (`id`, `dt`, `val`) 
VALUES
    (0, '2018-02-19 00:01:00', 10),
    (1, '2018-02-19 00:02:00', 10),
    (2, '2018-02-19 00:03:00', 10),
    (4, '2018-02-19 01:01:00', 10),
    (5, '2018-02-19 01:02:00', 10),
    (6, '2018-02-19 02:03:00', 10)
;

Sql:

select 
  lpad(cast(HH as char(2)),2,'0') as hour,  
  sum(val) as sumVal from
(
    select 
      EXTRACT(HOUR from dt) AS HH,
      val
    from T 
    WHERE dt >= '2018-02-19 0:0:0' and dt < '2018-02-20 0:0:0'

    UNION ALL SELECT  0,0
    UNION ALL SELECT  1,0 UNION ALL SELECT  2,0  UNION ALL SELECT  3,0
    UNION ALL SELECT  4,0 UNION ALL SELECT  5,0  UNION ALL SELECT  6,0
    UNION ALL SELECT  7,0 UNION ALL SELECT  8,0 UNION ALL SELECT  9,0
    UNION ALL SELECT 10,0 UNION ALL SELECT 11,0 UNION ALL SELECT 12,0
    UNION ALL SELECT 13,0 UNION ALL SELECT 14,0 UNION ALL SELECT 15,0
    UNION ALL SELECT 16,0 UNION ALL SELECT 17,0 UNION ALL SELECT 18,0
    UNION ALL SELECT 19,0 UNION ALL SELECT 20,0 UNION ALL SELECT 21,0
    UNION ALL SELECT 22,0 UNION ALL SELECT 23,0
) as m
group by lpad(cast(HH as char(2)),2,'0')

gets you an output of:

hour    sumVal
00  30
01  20
02  10
03  0
04  0
05  0
06  0
07  0
08  0
09  0
10  0
11  0
12  0
13  0
14  0
15  0
16  0
17  0
18  0
19  0
20  0
21  0
22  0
23  0

If you need this more often, create a numbers table and use the join syntax you already have. Without a numbers table you can as well union all without join.

SO-Readups:

With a numbers table the big union all above could be rewritten to

UNION ALL SELECT  num, 0 from numbers where num between 0 and 23

or you could use a join on it and your coalesce syntax.

Steven Chou · Answer 2 · 2018-02-19T14:42:55.443

try it:

SELECT
`AllHours`.`tmp_hour`,
COALESCE(SUM(`val`),0) AS `total`
    FROM
      tableA
    RIGHT JOIN (
    SELECT  0 AS `tmp_hour`
    UNION ALL SELECT  1 UNION ALL SELECT  2 UNION ALL SELECT  3
         UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6
         UNION ALL SELECT  7 UNION ALL SELECT  8 UNION ALL SELECT  9
         UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
         UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
         UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18
         UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21
         UNION ALL SELECT 22 UNION ALL SELECT 23
  )      AS `AllHours` ON DATE_FORMAT(dt, '%H') = `AllHours`.`tmp_hour`

    WHERE  `dt` BETWEEN '2018-02-19 00:00:00' AND '2018-02-19 23:59:59' OR `dt` IS NULL

    GROUP BY `AllHours`.`tmp_hour`
    ORDER BY `AllHours`.`tmp_hour`

Mysql , SUM column and GROUP BY HOUR

2 Answers2

Linked

Related