MySQL Query GROUP BY día / mes / año (Completo)

MySQL Query GROUP BY día / mes / año. – Abecés se requiere agrupar resultados por un determinado mes, usando la cláusula GROUP BY en un campo de tipo fecha, en este artículo veremos formas para poder agrupar fechas de una manera sencilla tanto en MySQL y SQLSERVER.

¿Cómo agrupar resultados por mes en una consulta SQL?

Básicamente, tenemos que extraer solo el mes de una fecha, para ello podemos usar clausulas predeterminadas de estas bases de datos.

MySQL Query GROUP BY día / mes / año

Para poder agrupar por dia, mes o año tenemos que usar las funciones que se pueden utilizar para manipular valores temporales en una consulta SQL.

A continuación, les dejaré las dos funciones más usadas y además la página oficial para que puedan consultar.

MONTH(date)

Devuelve el mes para la fecha, en el rango de 1 a 12 de enero a diciembre, o 0 para fechas como ‘0000-00-00’ o ‘2008-00-00’ que tienen una parte de mes cero.

mysql> SELECT MONTH('2008-02-03');
-> 2

MONTHNAME(date)

Devuelve el nombre completo del mes para la fecha. El idioma utilizado para el nombre está controlado por el valor de la variable de sistema lc_time_names (Sección 10.16, “Soporte de configuración regional del servidor MySQL”).

mysql> SELECT MONTHNAME('2008-02-03');
-> 'February'

Consulte las funciones de fecha y hora en MySQL.

Uso del ejemplo

Podemos agrupar por año y mes de acuerdo a una lista de fechas

GROUP BY YEAR(record_date), MONTH(record_date)

Año y mes GROUP BY

Si deseamos contar o sumar resultados por año y mes podemos usar la cláusula EXTRACT(YEAR_MONTH FROM fecha), que extrae información de un campo tipo fecha. Sin embargo, la utilidad de esto seria si queremos sumar las facturas de un determinado año y mes.

SELECT count(iduser) FROM usuarios GROUP BY EXTRACT(YEAR_MONTH FROM fecha);

Tambien intentemos usar la declaración ‘WHERE‘  la fórmula es correcta para la declaración WHERE, por lo que el código se quedaria de la siguiente manera:

SELECT COUNT(id) 
FROM stats 
WHERE YEAR(record_date) = 2021
GROUP BY MONTH(record_date)

Busqueda por varios años

Si su búsqueda es de varios años y aún desea agrupar mensualmente, sugiero lo siguiente:

Primera forma #1:

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY DATE_FORMAT(record_date, '%Y%m')

Segunda forma #2 (más eficiente):

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY YEAR(record_date)*100 + MONTH(record_date)

Comparé estas versiones en una tabla grande con 1357,918 filas (innodb), y la segunda versión parece tener mejores resultados.

• Primera forma (promedio de 10 ejecuciones): 1.404 segundos
• Segunda forma (promedio de 10 ejecuciones): 0,780 segundos

(Se agregó la clave SQL_NO_CACHE para evitar que MySQL haga CACHING en las consultas).

¿Cómo agrupar por mes usando SQL Server con PDO PHP?

En ocasiones de suele interactuar PHP con SQLSERVER para obtener consultas de esa base de datos. Sin embargo, se requiere de un driver de PHP para que puedan conectarse sin problemas.

sqlsrv_connect — Abre una conexión a una base de datos Microsoft SQL Server

Aquí veremos dos modelos de consultas para trabajar con agrupamientos en PHP SQLSERVER que suele ser un dolor de cabeza.

Si deseamos agrupar por meses primeramente podemos dividir en varios partes una columna de tipo fecha para poder realizar consultas. veamos los ejemplos.

SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)
ORDER BY [Month]

También puedes probar esta otra consulta SQL:

SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDER BY Year, Month

Restrinja la dimensión de NVARCHAR a 7, suministrada a CONVERT para mostrar solo «AAAA-MM»

SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)
ORDER BY [Month]

CONCLUSIÓN

Las clausulas GROUP BY de PHP suelen ser muy efectivas para agrupar resultados, pero si PHP se conecta a SQLSERVER haciendo uso de la conexión PDO, las cosas cambian y se vuelven más estrictas las consultas, ciclos.

Para agrupar en SQLSERVER PHP tenemos que seguir este criterio.

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

Si en el SELECT declaramos a Country y Region en GROUP BY también tenemos que colocar esas dos columnas de manera obligatoria para que funcione el filtro.

Espero que esta breve explicación les ayude en sus proyectos web.