MySQL: cuenta por mes(incluidos los registros faltantes)

join count

Question

Tengo este SELECCIONADO:

SELECT
  DATE_FORMAT(`created`, ''%Y-%m'') as byMonth,
  COUNT(*) AS Total 
FROM 
  `qualitaet`
WHERE
  `created` >= MAKEDATE(year(now()-interval 1 year),1) + interval 5 month
AND
  `status`=1
GROUP BY 
  YEAR(`created`), MONTH(`created`)
ORDER BY 
  YEAR(`created`) ASC

y obtén este resultado:

| byMonth | Total |
| 2015-06 |   2   |
| 2015-09 |  12   |
| 2015-10 |   3   |
| 2015-12 |   8   |
| 2016-01 |   1   |

ver SQL-Fiddle aquí

La cláusula WHERE es importante porque la necesito como el año fiscal actual que comienza el 1 de junio en mi ejemplo.

Como puede ver, no tengo registros para julio, agosto y noviembre. Pero necesito estos registros con cero en Total.

Entonces mi resultado debería verse así:

| byMonth | Total |
| 2015-06 |   2   |
| 2015-07 |   0   |
| 2015-08 |   0   |
| 2015-09 |  12   |
| 2015-10 |   3   |
| 2015-11 |   0   |
| 2015-12 |   8   |
| 2016-01 |   1   |

¿Hay alguna manera de obtener este resultado?

Answer 1

Debe generar todas las fechas deseadas, y luego dejar unir sus datos a las fechas. Tenga en cuenta también que es importante poner algunos predicados en la cláusula ON la combinación izquierda, y otros en la cláusula WHERE :

SELECT
  CONCAT(y, ''-'', LPAD(m, 2, ''0'')) as byMonth,
  COUNT(`created`) AS Total 
FROM (
  SELECT year(now())     AS y UNION ALL
  SELECT year(now()) - 1 AS y 
) `years`
CROSS JOIN (
  SELECT  1 AS m UNION ALL
  SELECT  2 AS m UNION ALL
  SELECT  3 AS m UNION ALL
  SELECT  4 AS m UNION ALL
  SELECT  5 AS m UNION ALL
  SELECT  6 AS m UNION ALL
  SELECT  7 AS m UNION ALL
  SELECT  8 AS m UNION ALL
  SELECT  9 AS m UNION ALL
  SELECT 10 AS m UNION ALL
  SELECT 11 AS m UNION ALL
  SELECT 12 AS m
) `months`
LEFT JOIN `qualitaet` q
ON YEAR(`created`) = y 
  AND MONTH(`created`) = m
  AND `status` = 1
WHERE STR_TO_DATE(CONCAT(y, ''-'', m, ''-01''), ''%Y-%m-%d'') 
    >= MAKEDATE(year(now()-interval 1 year),1) + interval 5 month
  AND STR_TO_DATE(CONCAT(y, ''-'', m, ''-01''), ''%Y-%m-%d'') 
    <= now()
GROUP BY y, m
ORDER BY y, m

¿Cómo funciona lo anterior?

• CROSS JOIN crea un producto cartesiano entre todos los años disponibles y todos los meses disponibles. Esto es lo que quiere, quiere todas las combinaciones de año-mes sin espacios.
• LEFT JOIN agrega todos los registros de qualitaet al resultado (si existen) y los une al producto cartesiano de un año anterior. Es importante poner prediactos como el predicado de status = 1 aquí.
• COUNT(created) cuenta solo valores no NULL de created , es decir, cuando LEFT JOIN no produce filas para un año-mes dado, queremos 0 como resultado, no 1 , es decir, no queremos contar el valor NULL .

Una nota sobre el rendimiento

Lo anterior hace un uso intensivo de operaciones de cadena y aritmética de fecha y hora en sus predicados ON y WHERE . Esto no funcionará para muchos datos. En ese caso, debe pre truncar e indexar sus meses-año en la tabla qualitaet , y operar solo en esos valores.