Agrupar por debe devolver 0 al agrupar por horas. ¿Como hacer esto?

Tengo una colisión similar a esta:

http://sqlfiddle.com/#!2/fecbc/3

Mi problema es que me gustaría cuando uso el group by HOUR(date) aparecen horas como 2 , 3 y 4 pero con 0 .

En realidad desaparecen del resultado.

¿Cómo puedo hacer esto?

¿Cuál es la manera simple de hacer esto?

¿Hay alguna manera de evitar 24 declaraciones ifs?

¿Podemos evitar crear otra tabla?

Necesitarás una tabla que contenga 24 horas, luego JOIN a ella. Si no puede crear una tabla, puede crearla dentro de su consulta. Vea esta demostración .

 SELECT a.hr, IFNULL(b.hourCount, 0) hourCount FROM (SELECT 1 AS hr 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 UNION ALL SELECT 0) a LEFT JOIN (SELECT COUNT(id) AS hourCount, HOUR(date) AS hr FROM `testing` GROUP BY HOUR(date)) b ON b.hr = a.hr 

Si no desea crear una tabla para esto, puede usar una subconsulta que enumera las horas del 0 al 23:

 SELECT v_hours.hr as hour, COUNT(id) AS hourCount FROM (select 0 as hr union all select 1 as hr union all select 2 as hr union all select 3 as hr union all select 4 as hr union all select 5 as hr union all select 6 as hr union all select 7 as hr union all select 8 as hr union all select 9 as hr union all select 10 as hr union all select 11 as hr union all select 12 as hr union all select 13 as hr union all select 14 as hr union all select 15 as hr union all select 16 as hr union all select 17 as hr union all select 18 as hr union all select 19 as hr union all select 20 as hr union all select 21 as hr union all select 22 as hr union all select 23 as hr ) v_hours LEFT JOIN testing ON HOUR(date) = v_hours.hr GROUP BY v_hours.hr ORDER BY v_hours.hr 

Use lpad en su consulta si quiere ceros a la izquierda.

 SELECT COUNT(id) AS hourCount, lpad(HOUR(date),2,'0') AS hour FROM testing GROUP BY HOUR(date) ; 

http://sqlfiddle.com/#!2/fecbc/5/0