MySQL: Grouped by hour, need to show all hours, null where no data

Question

Here's the query:

SELECT h.idhour, h.`hour`, outnumber, count(*) as `count`, sum(talktime) as `duration` 
FROM (
     SELECT 
        `cdrs`.`dcustomer` AS `dcustomer`,
        (CASE
            WHEN (`cdrs`.`cnumber` like "02%") THEN '02'
            WHEN (`cdrs`.`cnumber` like "05%") THEN '05'
        END) AS `outnumber`,
        FROM_UNIXTIME(`cdrs`.`start`) AS `start`,
         (`cdrs`.`end` - `cdrs`.`start`) AS `duration`,
         `cdrs`.`talktime` AS `talktime`
    FROM `cdrs`
    WHERE `cdrs`.`start` >= @_START and `cdrs`.`start` < @_END
    AND `cdrs`.`dtype` = _LATIN1'external'
    GROUP BY callid
   ) cdr 

   JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN 
        (SELECT id FROM customers WHERE parent = _ID))

   GROUP BY h.idhour, cdr.outnumber
   ORDER BY h.idhour;

The above query results skips the hours where there is no data, but I need to show all hours (00:00 to 23:00) with null or 0 values. How can I do this?

Answer 1

You need a table with all hours, nothing else.

Then use LEFT JOIN with the hours table on the "left" and your current query on the "right":

SELECT b.*
    FROM hours h
    LEFT JOIN ( ... ) b ON b.hr = h.hr
    WHERE h.hr BETWEEN ... AND ...
    ORDER BY hr; 

Any missing hours will be NULLs in b.*.

Answer 2

SELECT h.idhour
, h.hour
,IFNULL(outnumber,'') AS outnumber
,IFNULL(cdr2.duration,0) AS duration
,IFNULL(output_count,0) AS output_count
FROM hub.hours h
  LEFT JOIN ( 
 SELECT HOUR(start) AS start,outnumber, SUM(talktime) as duration ,COUNT(1) AS output_count
 FROM
 (
 SELECT cdrs.dcustomer AS dcustomer
 , (CASE WHEN (cdrs.cnumber like "02%") THEN '02' WHEN (cdrs.cnumber like "05%") THEN '05' END) AS outnumber
 , FROM_UNIXTIME(cdrs.start) AS start
 , (cdrs.end - cdrs.start) AS duration
 , cdrs.talktime AS talktime 
 FROM cdrs cdrs
 INNER JOIN customers c ON c.id = cdrs.dcustomer 
 WHERE cdrs.start >= @_START and cdrs.start < @_END AND cdrs.dtype = _LATIN1'external' 
 AND
 (c.parent = _ID or cdrs.dcustomer = _ID or c.parent IN (SELECT id FROM customers WHERE parent = _ID))
 GROUP BY callid 
 ) cdr 
 GROUP BY HOUR(start),outnumber
 ) cdr2 
 ON cdr2.start = h.idhour
 ORDER BY h.idhour