Mysql: Group by Hour, 0 if no data

Question

I have the following query:

SELECT count(*) as 'totalCalls', HOUR(`end`) as 'Hour'
FROM callsDataTable 
WHERE company IN (
    SELECT number 
    FROM products 
    WHERE products.id IN (@_PRODUCTS)) 
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH 
 group by HOUR(`end`)

Above query returns only the hours in which there where calls made:

totalCalls  Hour
    2       0
    1       2
    4       7
   98       8
  325       9
  629      10
  824      13
  665      15
  678      16
  665      17
  606      18     
   89      22
    5      23

The desired output should be all the hours, and where there are no calls it should be 0 calls for that hour, like below:

totalCalls  Hour
    0       0
    0       1
    1       2
    0       3
    0       4
    0       5
    0       6
    4       7
   98       8
  325       9
  629      10
    0      11
    0      12
  824      13
    0      14
  665      15
  678      16
  665      17
  606      18
    0      19
    0      20
    0      21
   89      22
    5      23

Well, there are `24` hours in a day. The expected outcome should start with a row having `0` in the `Hours` field. Except if you don't care about the calls that came in between (and including) `0:00:00` and `0:59:59`. — axiac, Feb 01 '15 at 13:02

Pரதீப் · Answer 1 · 2015-02-02T01:30:14.283

4

You need a Hour table and then do a left Outer Join with the Hour_table.

Which will ensure that all hours will be returned. If hour doesn't exists in callsDataTable then count will be 0.

Hours Table

create table hours_table (hours int);

insert into hours_table values(0);
insert into hours_table values(1);
 ...
insert into hours_table values(23);

Query:

SELECT count(HOUR(`end`)) as 'totalCalls', HT.Hours as 'Hour'
FROM Hours_table HT left Outer join callsDataTable CD
on HT.Hours = HOUR(`end`)
WHERE company IN (
    SELECT number 
    FROM products 
    WHERE products.id IN (@_PRODUCTS)) 
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH 
 group by HT.Hours

edited Feb 02 '15 at 01:30

answered Feb 01 '15 at 12:23

Pரதீப்

91,748
19
131
172

tried it and it doesn't return all the hours with 0 where there is no data, and it is pretty slow with 2.84 second – alex Feb 01 '15 at 12:54
I'm curious when `HOUR(end) = 24`. I guess that's the end of the world. :-) – axiac Feb 01 '15 at 13:04
@axiac is right. You want your hours_table to include a zero value. – O. Jones Feb 01 '15 at 20:09

score 3 · Accepted Answer · edited May 23 '17 at 11:57

First, your query can be expressed in a simpler way as:

SELECT COUNT(*) AS totalCalls, HOUR(`end`) AS `Hour`
FROM callsDataTable c
  INNER JOIN products p ON c.company = p.number
    AND p.id IN (@_PRODUCTS)
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH
GROUP BY HOUR(`end`) AS `Hour`
ORDER BY `Hour` ASC

Using the idea suggested by @NoDisplayName in their answer:

CREATE TABLE hours_table (hours INT);

INSERT INTO hours_table VALUES(0), (1), (2), 
    /* put the missing values here */ (23);

You can join the table that contains the hours to get the results you want:

SELECT COUNT(*) AS totalCalls, h.hours AS `Hour`
FROM callsDataTable c
  INNER JOIN products p ON c.company = p.number
  RIGHT JOIN hours_table h ON h.hours = HOUR(c.`end`)
    AND p.id IN (@_PRODUCTS)
    AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH
GROUP BY h.hours
ORDER BY h.hours ASC

If it runs too slow (and I'm sure it is very slow) you should investigate a way to use something like end BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 23:59:59' instead of comparing YEAR(end) and MONTH(end).

It can be accomplished like this:

SET @start = STR_TO_DATE(CONCAT(@_YEAR, '-', @_MONTH, '-01 00:00:00'), '%Y-%m-%d %H:%i:%s');
SET @end   = DATE_SUB(DATE_ADD(@start, INTERVAL 1 MONTH), INTERVAL 1 SECOND);

SELECT ...
...
    AND `end` BETWEEN @start AND @end
...

But this change doesn't help by itself. It needs an index on field end to bring the desired speed improvement:

ALTER TABLE callsDataTable ADD INDEX(end);

Using HOUR(c.end) in the join condition is another reason to run slowly.

It can be improved by joining the table hours_table with the result set produced by the (simplified version of the) first query:

SELECT IFNULL(totalCalls, 0) AS totalCalls, h.hours AS `Hour`
FROM hours_table h
   LEFT JOIN (
        SELECT COUNT(*) AS totalCalls, HOUR(`end`) as `Hour`
        FROM callsDataTable c
          INNER JOIN products p ON c.company = p.number
            AND p.id IN (@_PRODUCTS)
            AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH
        GROUP BY HOUR(`end`) AS `Hour`
   ) d ON h.hours = d.`Hour`
ORDER BY h.hours ASC

nice, will try this and get back – alex Feb 01 '15 at 14:08 — alex, Feb 01 '15 at 14:08

score 1 · Answer 3 · answered Feb 01 '15 at 13:00

The generation of values from nothing is not an easy job (usually it's not even possible) in MySQL.

I suggest a simpler approach:

generate in the client code a list of 24 entries (totalCalls, Hour) with 0 as totalCalls and the hours (from 0 to 23) as Hour. This is an easy task in any programming language.
run the query you already have, get the values it returns and use them to overwrite the empty values generated on the previous step.
enjoy.

Mysql: Group by Hour, 0 if no data

3 Answers3

Related