¿Cómo funciona la cláusula GROUP BY de SQL?

Agrupar resultados es una poderosa función de SQL que permite calcular estadísticas clave para un grupo de registros.

La cláusula GROUP BY es una de las más poderosas de SQL. Le permite ver los datos de una nueva manera y encontrar métricas clave (como el promedio, los valores máximos y mínimos en un grupo de registros).

Sin GROUP BY, todos los resultados que obtenemos están orientados a los registros. Con GROUP BY, podemos crear grupos de registros y calcular métricas en cada grupo. En este artículo, aprenderás cómo GROUP BY hace que tus consultas SQL sean mucho más potentes y diversas.

GROUP BY, Parte 1: Agrupación de datos

Supongamos que tenemos un pequeño hotel en la Patagonia. También tenemos una base de datos que contiene los nombres de los huéspedes, las ciudades de origen, las edades, las fechas de entrada, las fechas de salida, etc. Estos datos están en dos tablas llamadas room_guest y guest. Echa un vistazo:

room_guest

guest

Queremos calcular algunas estadísticas para poder reservar más huéspedes. La cláusula SQL GROUP BY nos permite agrupar los registros en función de los datos de una columna (o columnas) determinada. Podemos agrupar los registros de la tabla room_guest en función del valor de la columna origin_city. Así, todos los registros de huéspedes de "Génova" pertenecerán a un grupo; todos los registros de huéspedes de "Dubai" pertenecerán a otro grupo, y así sucesivamente. La siguiente tabla muestra cada grupo de registros en un color diferente.

Ahora, supongamos que el propietario del hotel quiere saber cuántos huéspedes vienen de cada ciudad. Para averiguarlo, necesitamos contar el número de registros de cada grupo. En otras palabras, necesitamos la función agregada COUNT(*), que devuelve el número de registros de un grupo. COUNT() es una función muy común; volveremos a ella más adelante en este artículo.

Por lo tanto, necesitamos una consulta para crear grupos de registros con el mismo valor en origin_city y luego contar el número de registros en cada grupo. La consulta tendría el siguiente aspecto:

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

Puede comparar el número de huéspedes de cada ciudad en la tabla de resultados que aparece a continuación con la tabla de colores mostrada anteriormente:

Observe que el número de filas en los resultados de la consulta es el mismo que la cantidad de grupos creados por la cláusula GROUP BY. Un grupo por cada ciudad, una fila por cada ciudad.

Para terminar esta introducción a GROUP BY, me gustaría sugerirte que leas el artículo Getting the Hang of the GROUP BY Clause. Incluye una descripción completa de GROUP BY y varios ejemplos de sus errores más comunes.

GROUP BY, Parte 2: Funciones de agregación

Aunque agrupar por un valor es útil, el verdadero poder de GROUP BY es cuando se utiliza con funciones de agregación. Me atrevería a decir que cada consulta de SQL que utilice una cláusula GROUP BY debería tener al menos una función de agregación. (Pero no es obligatorio).

En la sección anterior, mencionamos que GROUP BY se utiliza para crear grupos y calcular métricas. Las métricas se calculan mediante funciones de agregación como COUNT(), SUM(), AVG(), MIN() y MAX(). Los valores calculados por cada una de estas funciones se explican por sí mismos. Sin embargo, todas ellas tienen algo en común: todas las funciones de agregación devuelven un valor basado en todos los registros del grupo.

Veamos un ejemplo. El propietario del hotel quiere saber el valor máximo facturado por cada habitación. Además, quiere ver el valor mínimo y medio facturado por cada habitación. Esta es la consulta, seguida de los resultados:

SELECT 	room_number,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number

Agrupación de registros por múltiples columnas

En algunos casos, podemos necesitar agrupar por dos o más columnas. ¿Podemos hacerlo con GROUP BY? Por supuesto que sí.

En la consulta anterior, creamos un informe que analiza cuánto dinero está generando cada sala. Sin embargo, algunas habitaciones pueden estar configuradas en un nivel premium o estándar (véase el número de habitación 2002) durante diferentes temporadas; por lo tanto, para hacer un análisis correcto, necesitamos agrupar los registros utilizando dos columnas: room_number y room_level.

Antes de pasar a la consulta, vamos a utilizar colores para ver cómo se agrupan los registros mediante la cláusula GROUP BY room_number, room_level. Recuerde que los registros de cada grupo deben tener exactamente los mismos valores en room_number y room_level. Por ejemplo, el primer grupo es para room_number = 1001 y room_level = ‘standard’.

La consulta es:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number, room_level

La siguiente tabla muestra los resultados de esta consulta. Puede comparar esta tabla con la anterior para verificar los resultados.

Agrupación de valores nulos

Como cualquier otro valor, los valores de NULL tienen su propio grupo; si tenemos un NULL en cualquiera de las columnas de GROUP BY, se crea un grupo extra de registros para esos registros. Para demostrarlo, tenemos que insertar un par de registros con valores NULL en la columna origin_city:

INSERT INTO into room_guest VALUES ('Kevin C.', NULL, 2001, '2019-07-25', '2019-08-07', NULL, 'standard', 10500);
INSERT INTO into room_guest VALUES  ('Karl J.', NULL, 1002, '2019-11-12', '2019-11-22', NULL, 'premium', 13900);

Entonces, esta consulta ...

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

... mostrará el siguiente resultado. Observe el nuevo grupo para los valores NULL origin_city en la primera fila:

Uso de WHERE con GROUP BY

La cláusula WHERE se utiliza con frecuencia en las consultas de SQL, por lo que es importante entender cómo funciona cuando se combina con GROUP BY.

La cláusula WHERE se aplica antes que la GROUP BY. Esto significa que todos los registros se filtran primero por WHERE; luego los registros que coinciden con la condición WHERE se agrupan utilizando el criterio GROUP BY.

Como ejemplo, utilicemos la consulta anterior, pero esta vez filtraremos los huéspedes procedentes de las ciudades de San Francisco y Los Ángeles. La consulta es:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest
WHERE		origin_city IN (‘San Francisco’,’Los Angeles’ )
GROUP BY	room_number, room_level

Como era de esperar, este conjunto de resultados es más corto que los anteriores; la cláusula WHERE filtró a muchos huéspedes, y sólo los registros de las habitaciones de San Francisco y Los Ángeles fueron procesados por la cláusula GROUP BY.

Cómo evitar problemas con GROUP BY

Cuando se empieza a utilizar GROUP BY, es común encontrarse con los siguientes problemas. A continuación se explica cómo evitarlos.

Problemas de conteo

Veamos un caso similar en el que necesitamos añadir más de una columna extra en la cláusula GROUP BY. En la primera consulta, agrupamos por origin_city. Sin embargo, algunas ciudades comparten el mismo nombre (porque están en diferentes estados o países). En nuestro conjunto de datos, tenemos dos ciudades diferentes llamadas San Pedro, una en Argentina y otra en España. No queremos contarlas juntas, ya que son dos lugares diferentes.

Para contar estas ciudades por separado, tenemos que agrupar los registros utilizando las columnas city_origin, state, y country. A continuación, repetiremos la primera consulta pero añadiremos las columnas state y country a la cláusula GROUP BY. Sin embargo, si añadimos columnas a la cláusula GROUP BY, también debemos añadirlas a la cláusula SELECT.

Como las columnas estado y país están en la guest tenemos que JOIN las tablas room_guest y guest. Esta es la consulta que tenemos:

SELECT 	origin_city, state, country
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Los resultados muestran dos ciudades "San Pedro" diferentes porque hemos utilizado state y country como columnas adicionales en la cláusula GROUP BY.

Todavía hay un problema que solucionar en esta consulta: si la misma persona visitó el hotel dos veces, estamos contando esta persona dos veces. Esto no es necesariamente incorrecto, pero ¿qué pasa si queremos saber el número de visitantes únicos del hotel? Tendríamos que utilizar COUNT(distinct guest_name). La función de agrupación COUNT(distinct column) devuelve la cantidad de valores únicos de una columna determinada en un grupo de registros.

En la consulta siguiente, añadimos la función COUNT(distinct). También mantenemos el COUNT(*) original para que el lector pueda comparar ambos resultados:

SELECT 	origin_city, state, country
COUNT(distinct guest_name) AS number_of_unique_guests,
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Ahora podemos ver que el hotel recibió un total de tres visitas de un residente de Dubai, pero que estas tres visitas fueron realizadas por dos personas distintas (Peter S. y Olek V) .

Antes de cerrar esta sección, te sugiero que veas este vídeo de 5 minutos sobre GROUP BY para principiantes. Es una forma súper dinámica de aprender SQL.

Omitir columnas no agregadas en GROUP BY

Otro error muy común de GROUP BY es añadir una columna no agregada (es decir, una columna que no se utiliza en una función agregada) en el SELECT que no tiene en GROUP BY. Para evitar este error, siga una regla muy sencilla: Todas las columnas de SELECT deben aparecer en la cláusula GROUP BY o utilizarse en una función de agregación.

Probemos una consulta no válida para ver el error:

SELECT    room_number,
	    room_level,
	    origin_city, --This column is invalid, is not in the GROUP BY
    COUNT(*) AS quantity_of_visitors,
FROM      room_guest 
GROUP BY  room_number, room_level

Si ejecutamos esta consulta, obtendremos el siguiente error:

ERROR:  The column «room_guest.origin_city» must be in the GROUP BY clause
LINE 3:   guest_age,

Podemos solucionar el error añadiendo la columna origin_city a la cláusula GROUP BY:

	SELECT 	room_number,
			room_level,
			Origin_city,
			COUNT(*) AS quantity_of_visitors
	FROM		room_gest
	GROUP BY	room_number, room_level, origin_city -- origin_city added

Si estás tratando de entender la diferencia entre GROUP BY y ORDER BY, lee la Diferencia entre GROUP BY y ORDER BY en palabras simples. Te ayudará a resolverlo.

Hay más cosas que hacer con GROUP BY

Hemos aprendido a utilizar GROUP BY para agrupar registros por valores comunes. Sabemos que las funciones de agregación MIN(), MAX(), AVG(), y SUM() calculan varias estadísticas. Y la función COUNT() hace muchas cosas:

• COUNT(*) cuenta todas las filas.
• COUNT(guest_name) cuenta todos los valores no NULL en la columna nombre_huésped.
• COUNT(distinct guest_name) cuenta todos los valores diferentes no NULL en la columna guest_name.

Al agrupar, NULL obtiene su propio grupo. Y todas las columnas no agregadas en SELECT deben estar presentes en GROUP BY.

Debido a la longitud del artículo, no he cubierto la cláusula HAVING, que es una especie de cláusula WHERE utilizada para filtrar grupos en lugar de registros. Para aquellos lectores que quieran ir un paso más allá, les dejaré un enlace a nuestro SQL para principiantes curso, que cubre muchos temas interesantes. ¡Es una gran manera de desarrollar tus habilidades en SQL!