Mysql: Group by Hour, 0 if no data

alex

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
axiac

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related