Totales Desde El Query (WITH ROLLUP)

publicado en mysql, reportes

En el 99.99999999999% de los reportes nos piden totales, estos totales en un porcentaje casi similar se “tienen” que procesar en algún lenguaje, en este post se verá una forma de hacer esos totales sin procesar la consulta fuera de MySQL.

Supongamos que tenemos la tabla de Facturas y queremos sacar las ventas por año teniendo los siguientes datos:

Factura:

  • id
  • fecha
  • total

Digamos que queremos el reporte de las ventas por año, vamos a tener un query como este:

1
2
3
4
SELECT DATE_FORMAT(fecha, "%Y") AS anio,
       FORMAT(SUM(`total`), 2) AS total
FROM facturas
GROUP BY anio

Como podemos observar, al ejecutar el query obtenemos los totales por año y tendríamos que procesarlo de manera separada para poder sacar el gran total.

+------+------------+
| anio | total      |
+------+------------+
| 2012 | 111,932.17 |
| 2013 | 244,069.07 |
| 2014 | 231,421.45 |
+------+------------+

Aquí es donde entra la magia de la sentencia WITH ROLLUP. Esta sentencia se va a encargar de agregar un registro extra con el total de la columna y los demás valores los va a dejar en NULL veamos.

1
2
3
SELECT DATE_FORMAT(fecha, "%Y") AS anio,
       FORMAT(SUM(`total`), 2) AS total
FROM facturas GROUP BY anio WITH ROLLUP
+------+------------+
| anio | total      |
+------+------------+
| 2012 | 111,932.17 |
| 2013 | 244,069.07 |
| 2014 | 231,421.45 |
| NULL | 587,422.69 |
+------+------------+

Como podemos observar ya se está calculando el gran total de las ventas, pero ahora nos vemos en el dilema de mandarlo bonito al cliente, no queremos que lea NULL y no sepa que es o llame preguntando a las 4am que se generó su archivo.

OJO: sólo si el campo que estamos manejando no tiene ninguna modificación se puede usar IFNULL o COALESCE para eliminar el NULL del campo extra.

1
2
3
SELECT COALESCE(year, "Total: ") AS anio,
       FORMAT(SUM(`total`), 2) AS total
FROM facturas GROUP BY year WITH ROLLUP
+---------+------------+
| anio    | total      |
+---------+------------+
| 2012    | 111,932.17 |
| 2013    | 244,069.07 |
| 2014    | 231,421.45 |
| Total:  | 587,422.69 |
+---------+------------+

Una vez hecha esta aclaración…proseguimos.

Entonces vamos a modificar un poco el query para que nos entregue resultados bonitos:

1
2
3
4
5
SELECT IFNULL(anio, "TOTAL") AS "Año", subtotal AS "Subtotal" FROM (
  SELECT DATE_FORMAT(fecha, "%Y") AS anio,
         FORMAT(SUM(`total`), 2) AS subtotal
     FROM facturas GROUP BY anio WITH ROLLUP
) AS Tabla #este solo es un alias para que funcione la consulta
+-------+------------+
| Año   | Subtotal   |
+-------+------------+
| 2012  | 111,932.17 |
| 2013  | 244,069.07 |
| 2014  | 231,421.45 |
| TOTAL | 587,422.69 |
+-------+------------+

Tenemos que hacer el subquery ya que las columnas calculadas son añadidas al final de la consulta por lo que no podríamos utilizar las funciones IFNULL o COALESCE (vease advertencia de arriba :3).

Una de las cosas que tenemos que considerar con el uso de WITH ROLLUP es que no podemos usar ORDER BY ya que son excluyentes entre ellos.

Pero entonces tendríamos un grave problema si queremos ordenar por año de manera descendente, sin embargo para esto podemos poner el orden que queremos directamente en la clausula GROUP BY de la siguiente manera:

1
2
3
4
5
SELECT IFNULL(anio, "TOTAL") AS "Año", subtotal AS "Subtotal" FROM (
  SELECT DATE_FORMAT(fecha, "%Y") AS anio,
         FORMAT(SUM(`total`), 2) AS subtotal
     FROM facturas GROUP BY anio DESC WITH ROLLUP
) AS Tabla #este sólo es un alias para que funcione la consulta
+-------+------------+
| Año   | Subtotal   |
+-------+------------+
| 2014  | 231,421.45 |
| 2013  | 244,069.07 |
| 2012  | 111,932.17 |
| TOTAL | 587,422.69 |
+-------+------------+

Un ejemplo más complejo sería sacar los totales parciales por mes y el gran total en una sola consulta. Esto se logra con un query como el siguiente:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT IF(anio IS NULL AND mes_num IS NULL, "",
           IF(mes_num IS NULL AND anio IS NOT NULL, "", anio)) AS "Año",
       IFNULL(mes_num, "") AS "mes",
       IF(anio IS NULL AND mes_num IS NULL, "GRAN TOTAL",
          IF(mes_num IS NULL AND anio IS NOT NULL, CONCAT("SubTotal (",anio,")"), mes)) AS "Mes",
       total FROM (
         SELECT DATE_FORMAT(fecha, "%Y") AS anio,
                DATE_FORMAT(fecha, "%m") AS mes_num,
                DATE_FORMAT(fecha, "%M") AS mes,
                FORMAT(SUM(`total`), 2) AS total
         FROM facturas
           GROUP BY anio DESC,
                    mes_num DESC WITH ROLLUP
) AS Tabla #este sólo es un alias para que funcione la consulta

Y nos quedaría algo así.

+------+-----+-----------------+------------+
| Año  | mes | Mes             | total      |
+------+-----+-----------------+------------+
| 2014 | 08  | August          | 11,756.03  |
| 2014 | 07  | July            | 28,404.67  |
| 2014 | 06  | June            | 31,201.27  |
| 2014 | 05  | May             | 19,785.91  |
| 2014 | 04  | April           | 29,196.59  |
| 2014 | 03  | March           | 29,939.79  |
| 2014 | 02  | February        | 21,246.03  |
| 2014 | 01  | January         | 59,891.16  |
|      |     | SubTotal (2014) | 231,421.45 |
| 2013 | 12  | December        | 7,213.51   |
| 2013 | 11  | November        | 33,693.84  |
| 2013 | 10  | October         | 7,458.48   |
| 2013 | 09  | September       | 44,610.03  |
| 2013 | 08  | August          | 7,944.63   |
| 2013 | 07  | July            | 21,083.25  |
| 2013 | 06  | June            | 15,023.43  |
| 2013 | 05  | May             | 19,780.90  |
| 2013 | 04  | April           | 17,759.92  |
| 2013 | 03  | March           | 16,482.20  |
| 2013 | 02  | February        | 22,169.25  |
| 2013 | 01  | January         | 30,849.63  |
|      |     | SubTotal (2013) | 244,069.07 |
| 2012 | 12  | December        | 30,348.10  |
| 2012 | 11  | November        | 24,823.21  |
| 2012 | 10  | October         | 23,318.44  |
| 2012 | 09  | September       | 26,822.99  |
| 2012 | 08  | August          | 6,619.43   |
|      |     | SubTotal (2012) | 111,932.17 |
|      |     | GRAN TOTAL      | 587,422.69 |
+------+-----+-----------------+------------+

Aquí ya tenemos un reporte bastante completo, debemos observar que si tenemos más de una columna en GROUP BY “ROLLUP” va a hacer un corte por cada cambio de grupo.

Aprovechando que ando recuperando posts en este recibí varios comentarios y anexo las respuestas correspondientes :)

Andres / julio 30, 2013

Un favor, alguna alternativa para que salga el nombre de los meses en español? Grax !

Desde la conexión actual puedes ejecutar lo siguiente para saber el “locale” que tienes configurado

SELECT @@lc_time_names; #Te debería de dar algo como “en_US”

Para cambiarlo deberás ejecutar lo siguiente

SET @@lc_time_names = "es_MX";

OJO: esto solo aplica para la conexión actual.

Para hacer el cambio permanente hay dos opciones.

  • Si tienes un usuario con el permiso SUPER ejecutar lo siguiente.

SET GLOBAL lc_time_names = "es_MX";

Salir de la conexión actual y volver a entrar

  • En el archivo my.cnf agregar la siguiente línea en la zona [mysqld]

lc_time_names = es_MX

Ejemplo:

+------+-----+-----------------+------------+
| Año  | mes | Mes             | total      |
+------+-----+-----------------+------------+
| 2014 | 08  | agosto          | 11,756.03  |
| 2014 | 07  | julio           | 28,404.67  |
| 2014 | 06  | junio           | 31,201.27  |
...

Isaac / agosto 16, 2013

Hola! Estoy haciendo algo similar, y de tu ejemplo me quiero guiar, tendrás el script de la base de datos que utilizas para hacer pruebas? Saludos, un excelente aporte.

Les dejo el link de los datos usados en este post:

Datos de prueba

También les dejo un link para generar datos de prueba.

http://www.generatedata.com/

Fred / septiembre 14, 2014

Amigo, excelente aporte!!!. Ayúdame en lo siguiente. Si además de ese reporte, quiero añadir una columna de PORCENTAJE, que compare la venta mensual al año que le corresponde.

Año Mes Total Porc 2014 04 400 0.20 2014 03 200 0.10 2014 02 800 0.40 2014 01 600 0.30 Subtotal (2014) 2000 1

Muchas gracias de antemano!!!

Quedaría un query parecido a este:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT IF(anio IS NULL AND mes_num IS NULL, "",
           IF(mes_num IS NULL AND anio IS NOT NULL, "", anio)) AS "Año",
       IFNULL(mes_num, "") AS "mes",
       IF(anio IS NULL AND mes_num IS NULL, "GRAN TOTAL",
          IF(mes_num IS NULL AND anio IS NOT NULL, CONCAT("SubTotal (",anio,")"), mes)) AS "Mes",
           IF(mes_num IS NULL AND anio IS NULL, "",FORMAT((total *100 / (SELECT SUM(total) FROM facturas WHERE DATE_FORMAT(fecha, "%Y") LIKE anio GROUP BY anio)),2)) AS porcentaje,
       FORMAT(total,2) AS total FROM (
         SELECT DATE_FORMAT(fecha, "%Y") AS anio,
                DATE_FORMAT(fecha, "%m") AS mes_num,
                DATE_FORMAT(fecha, "%M") AS mes,
                SUM(`total`) AS total
         FROM facturas
           GROUP BY anio DESC,
                    mes_num DESC WITH ROLLUP
) AS Tabla #este sólo es un alias para que funcione la consulta

Las diferencias radican en lo siguiente:

Se agrega esta linea antes del total:

1
IF(mes_num IS NULL AND anio IS NULL, "",FORMAT((total *100 / (SELECT SUM(total) FROM facturas WHERE DATE_FORMAT(fecha, "%Y") LIKE anio GROUP BY anio)),2)) AS porcentaje,

Se hace el sum total por año para poder hacer el cálculo, con el cache activo este subquery debería de ser bastante rápido

Se elimina el FORMAT de la segunda parte del query para que haga el cálculo correctamente

1
2
3
4
5
...
DATE_FORMAT(fecha, "%M") AS mes,
                FORMAT(SUM(`total`), 2) AS total # se cambia a SUM(`total`) AS total
         FROM facturas
...

Basados en nuestro ejemplo quedaría algo así:

+------+-----+-----------------+------------+------------+
| Año  | mes | Mes             | porcentaje | total      |
+------+-----+-----------------+------------+------------+
| 2014 | 08  | agosto          | 5.08       | 11,756.03  |
| 2014 | 07  | julio           | 12.27      | 28,404.67  |
| 2014 | 06  | junio           | 13.48      | 31,201.27  |
| 2014 | 05  | mayo            | 8.55       | 19,785.91  |
| 2014 | 04  | abril           | 12.62      | 29,196.59  |
| 2014 | 03  | marzo           | 12.94      | 29,939.79  |
| 2014 | 02  | febrero         | 9.18       | 21,246.03  |
| 2014 | 01  | enero           | 25.88      | 59,891.16  |
|      |     | SubTotal (2014) | 100.00     | 231,421.45 |
| 2013 | 12  | diciembre       | 2.96       | 7,213.51   |
| 2013 | 11  | noviembre       | 13.81      | 33,693.84  |
| 2013 | 10  | octubre         | 3.06       | 7,458.48   |
| 2013 | 09  | septiembre      | 18.28      | 44,610.03  |
| 2013 | 08  | agosto          | 3.26       | 7,944.63   |
| 2013 | 07  | julio           | 8.64       | 21,083.25  |
| 2013 | 06  | junio           | 6.16       | 15,023.43  |
| 2013 | 05  | mayo            | 8.10       | 19,780.90  |
| 2013 | 04  | abril           | 7.28       | 17,759.92  |
| 2013 | 03  | marzo           | 6.75       | 16,482.20  |
| 2013 | 02  | febrero         | 9.08       | 22,169.25  |
| 2013 | 01  | enero           | 12.64      | 30,849.63  |
|      |     | SubTotal (2013) | 100.00     | 244,069.07 |
| 2012 | 12  | diciembre       | 27.11      | 30,348.10  |
| 2012 | 11  | noviembre       | 22.18      | 24,823.21  |
| 2012 | 10  | octubre         | 20.83      | 23,318.44  |
| 2012 | 09  | septiembre      | 23.96      | 26,822.99  |
| 2012 | 08  | agosto          | 5.91       | 6,619.43   |
|      |     | SubTotal (2012) | 100.00     | 111,932.17 |
|      |     | GRAN TOTAL      |            | 587,422.69 |
+------+-----+-----------------+------------+------------+


Comentarios