MySQL - Agrupar por meses y años

Pos: 40

Val: 22

Agrupar por meses y años

Publicado por Jesus (12 intervenciones) el 19/03/2018 19:40:13

Hola a todos, de antemano vaya por delante mi agradecimiento por estar ahí. Recurro a vosotros para construir una SQL con la que no consigo el resultado esperado. De una tabla de facturas de ventas (facturasventas) pretendo obtener los resultados de las ventas agrupadas por meses y años. Hice lo siguiente:

SELECT Mes,A_2015,A_2016, A_2017, A_2015+A_2016+A_2017 AS Total

 FROM (SELECT MONTH(Fecha) AS Mes

, SUM(IF(YEAR(Fecha)=2015,Total,0)) As 'A_2015'

, SUM(IF(YEAR(Fecha)=2016,Total,0)) As 'A_2016'

, SUM(IF(YEAR(Fecha)=2017,Total,0)) As 'A_2017'

 FROM facturasventas fv INNER JOIN clientes c ON fv.idCliente=c.id

 WHERE YEAR(Fecha) between 2015 and 2017

 GROUP BY Mes) AS suma

Con esta SQL obtengo el siguiente resultado:

Consulta_Agrupada_meses_anos0

como se puede observar no sale Agosto porque no hay ventas (vacaciones) pero me gustaria que saliera el mes aun estando con 0. y si es posible el nombre del mes con letra, es decir, el resultado que necesito sería:

Consulta_Agrupada_meses_anos

Saludos y gracias por compartir vuestros conocimientos.

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 1

Val: 953

Agrupar por meses y años

Publicado por Leonardo Josué (414 intervenciones) el 20/03/2018 16:04:42

Hola Jesus:

Lamentablemente las bases de datos trabajan SÓLO CON LOS DATOS QUE EXISTEN EN LA MISMA BASE DE DATOS, es decir NO PUEDEN INVENTAR INFORMACIÓN. En tu caso, si no hay datos para el MES 8 entonces NO PUEDES INVENTAR UN CERO

¿Qué tendrías que hacer para resolver tu problema?, puedes crear una tabla QUE CONTENGA TODOS LOS MESES DE TODOS LOS AÑOS que te interesa y después hacer un LEFT JOIN a tu consulta:

imagina que tienes estos datos:

mysql> SELECT * FROM tabla;

+----------+---------------------+-------+

| id_tabla | fecha               | monto |

+----------+---------------------+-------+

|        1 | 2018-01-01 00:00:00 |    10 |

|        2 | 2018-01-01 00:00:00 |    13 |

|        3 | 2018-02-01 00:00:00 |    14 |

|        4 | 2018-02-01 00:00:00 |    50 |

|        5 | 2018-02-01 00:00:00 |    17 |

|        6 | 2018-04-01 00:00:00 |     5 |

|        7 | 2018-04-01 00:00:00 |    11 |

|        8 | 2018-06-01 00:00:00 |    21 |

|        9 | 2018-06-01 00:00:00 |    32 |

+----------+---------------------+-------+

9 rows in set (0.00 sec)

Si agrupas por mes, como lo estás haciendo obtienes esto:

mysql> SELECT MONTH(Fecha) Mes, SUM(monto) total_mes

    ->  FROM tabla

    ->  GROUP BY Mes;

+------+-----------+

| Mes  | total_mes |

+------+-----------+

|    1 |        23 |

|    2 |        81 |

|    4 |        16 |

|    6 |        53 |

+------+-----------+

4 rows in set (0.00 sec)

Aquí se observa que para los MESES 3 y 5 NO HAY MONTOS, entonces lo que tienes que hacer es crear una tabla CON TODOS LOS MESES QUE TE INTERESA:

mysql> SELECT * FROM meses;

+--------+------+------+-------------+

| id_mes | anio | mes  | descripcion |

+--------+------+------+-------------+

|      1 | 2018 |    1 | ENERO       |

|      2 | 2018 |    2 | FEBRERO     |

|      3 | 2018 |    3 | MARZO       |

|      4 | 2018 |    4 | ABRIL       |

|      5 | 2018 |    5 | MAYO        |

|      6 | 2018 |    6 | JUNIO       |

+--------+------+------+-------------+

6 rows in set (0.00 sec)

Entonces a partid de esta tabla HACES UN LEFT JOIN A LA CONSULTA QUE AGRUPA LOS DATOS:

mysql> SELECT meses.descripcion, T1.total_mes

    ->  FROM meses

    ->  LEFT JOIN

    ->  (  SELECT MONTH(Fecha) Mes, SUM(monto) total_mes

    ->     FROM tabla

    ->     GROUP BY Mes) T1 ON T1.mes = meses.mes;

+-------------+-----------+

| descripcion | total_mes |

+-------------+-----------+

| ENERO       |        23 |

| FEBRERO     |        81 |

| MARZO       |      NULL |

| ABRIL       |        16 |

| MAYO        |      NULL |

| JUNIO       |        53 |

+-------------+-----------+

6 rows in set (0.00 sec)

o utilizas la función IFNULL para cambiar el NULL por el valor de cero:

mysql> SELECT meses.descripcion, IFNULL(T1.total_mes, 0) total_mes

    ->  FROM meses

    ->  LEFT JOIN

    ->  (  SELECT MONTH(Fecha) Mes, SUM(monto) total_mes

    ->     FROM tabla

    ->     GROUP BY Mes) T1 ON T1.mes = meses.mes;

+-------------+-----------+

| descripcion | total_mes |

+-------------+-----------+

| ENERO       |        23 |

| FEBRERO     |        81 |

| MARZO       |         0 |

| ABRIL       |        16 |

| MAYO        |         0 |

| JUNIO       |        53 |

+-------------+-----------+

6 rows in set (0.00 sec)

¿Se entiende? Haz la prueba y nos comentas.

Saludos
Leo.

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 40

Val: 22

Agrupar por meses y años

Publicado por Jesus (12 intervenciones) el 21/03/2018 12:43:11

Leo, muchas gracias por tu respuesta. Sin ánimo de contradecirte pienso que no es verdad que una consulta solo pueda arrojar datos que esten grabados en la base de datos. Sin inventar información, como me comentas, el ejemplo de tu respuesta se puede transcribir sin necesidad de crear una tabla de meses, de la siguiente manera:

SELECT TMeses.Mes,T1.total_mes FROM

 (SELECT 1 as IdMes , 'Enero'     as Mes UNION

 SELECT 2 as IdMes , 'Febrero'    as Mes UNION

 SELECT 3 as IdMes , 'Marzo'      as Mes UNION

 SELECT 4 as IdMes , 'Abril'      as Mes UNION

 SELECT 5 as IdMes , 'Mayo'       as Mes UNION

 SELECT 6 as IdMes , 'Junio'      as Mes UNION

 SELECT 7 as IdMes , 'Julio'      as Mes UNION

 SELECT 8 as IdMes , 'Agosto'     as Mes UNION

 SELECT 9 as IdMes , 'Septiembre' as Mes UNION

 SELECT 10 as IdMes, 'Octubre'    as Mes UNION

 SELECT 11 as IdMes, 'Noviembre'  as Mes UNION

 SELECT 12 as IdMes, 'Diciembre'  as Mes) TMeses

LEFT JOIN

    (SELECT MONTH(Fecha) Mes, SUM(Total) total_mes FROM facturasventas WHERE YEAR(Fecha)=2017 GROUP BY Mes) T1

ON T1.Mes = TMeses.idMes

Yo en mi solicitud de ayuda (como expuse en mi primer SELECT de ejmplo) intento obtener una tabla agrupada por meses y pivotada por años, esto es precisamente lo que no logro diseñar. Como ves en mi primer ejemplo en un solo select pivoto por años sean 3 o 10 años, entiendo que puedo hacer 10 select uno por cada año pero no me parece la mejor solucion. En fin ruego que no me malinterpretes esta contestación, solo busco la mejor solucion para lo que intento obtener. Gracias.

Valora esta respuesta

-1

Comentar

Pos: 1

Val: 953

Agrupar por meses y años

Publicado por Leonardo Josué (414 intervenciones) el 21/03/2018 15:46:44

Hola de nuevo Jesus:

Sin ánimo de contradecirte pienso que no es verdad que una consulta solo pueda arrojar datos que esten

grabados en la base de datos. Sin inventar información, como me comentas, el ejemplo de tu respuesta se

puede transcribir sin necesidad de crear una tabla de meses, de la siguiente manera:

Mucho ojo Jesús, yo en ningún lado dije que los datos tenían que estar GRABADOS en la base de datos sino que EXISTAN en la Base de datos, que es muy distinto.

Si checas mi post te puse que UNA DE LAS FORMAS para obtener lo que querías era creando una tabla física con los meses, pero en ningún caso dije que fuera la única ni mucho menos la mejor. Efectivamente, tal como lo mencionas una forma de hacer lo mismo es crear una tabla virtual o "al vuelo" en lugar de tener los datos en una tabla física, pero esto hace justamente que los datos EXISTAN en la misma base, aunque sea de manera virtual.

En otras palabras, vuelvo a comentar lo mismo que puse al inicio de mi post:

Lamentablemente las bases de datos trabajan SÓLO CON LOS DATOS QUE EXISTEN EN LA MISMA BASE DE DATOS

Saludos
Leo.

Valora esta respuesta

Comentar

Agrupar por meses y años

Publicado por cristian reyes (1 intervención) el 11/06/2022 23:27:18

gracias, me ayudo mucho tu respuesta.

Valora esta respuesta

Comentar

Lista de fecha con intervalos

consulta sobre base de datos para altererar orden de una columna