Clausula GROUP BY en SQL, Agrupar las filas y contarlas

La sentencia GROUP BY identifica una columna seleccionada para utilizarla para agrupar resultados. Divide los datos en grupos por los valores de la columna especificada, y devuelve una fila de resultados para cada grupo.

Se puede utilizar GROUP BY con más de un nombre de columna (separando los nombres de columna con comas). Es necesario colocar siempre GROUP BY después de FROM y WHERE en una consulta, y antes de HAVING y ORDER BY.

Todas las columnas seleccionadas sin una agregación asociada deben aparecer en la cláusula GROUP BY.

SELECT   id_cliente,   COUNT(*) FROM tarjeta GROUP BY id_cliente;

GROUP BY agrupa todas las filas que tengan el mismo valor en la columna especificada.

En este ejemplo, todas las tarjetas  pertenecientes al mismo cliente se agruparán en una fila. Después, la función COUNT(*) contará todas las filas de los clientes. Como resultado, obtendremos una tabla en la que aparecerá cada id_cliente junto con el número de tarjetas de las que es titular.

Por ejemplo para obtener el número de empleados de cada departamento en el año 2023. Mostramos el nombre del departamento junto con el número de empleados. Y llamamos a la segunda columna numero_de_empleados

SELECT departamento, COUNT(*) AS numero_de_empleados FROM empleados WHERE fecha = 2023 GROUP BY departamento;

Obtener los valores mínimos y máximos de grupos

GROUP BY se puede utilizar junto con muchas otras funciones.

SELECT   id_cliente,   MAX(saldo_total) FROM tarjetas GROUP BY id_cliente;

Se ha sustituido COUNT(*) por MAX(saldo_total).

En lugar de contar todos las trajetas de determinados clientes, obtendremos el saldo de tarjeta con el valor más alto de cada cliente.

Obtener el promedio de grupos

SELECT  id_cliente,  AVG(saldo_total) FROM tarjetas WHERE fecha_de_compra >= '2023-01-01'   AND fecha_de_compra < '2024-01-01' GROUP BY id_cliente;

Utilizamos la función AVG(saldo_total)  para obtener el promedio de los pedidos de cada uno de nuestros clientes, pero solo para los pedidos que realizaron en 2023.

Agrupar por varias columnas

A veces queremos agrupar las filas por más de una columna. Imaginemos que tenemos unos cuantos clientes que hacen montones de compras cada día, y que nos gustaría saber el importe que gastan diariamente en sus compras.

SELECT  id_cliente,  fecha_de_compra,  SUM(importe) FROM tarjetas GROUP BY id_cliente, fecha_de_compra;

Como se puede ver, agrupamos por dos columnas (id_cliente y fecha_de_compra). Seleccionamos estas columnas y la función SUM(importe).

Recordar: en este tipo de consultas, todas las columnas de la parte SELECT deben usarse posteriormente para la agrupación o con una de las funciones.

Filtrar grupos

Veremos cómo se pueden filtrar los grupos. Existe una palabra clave especial (HAVING) que está reservada para ello.

SELECT  id_cliente,  fecha_de_compra,  SUM(importe) FROM tarjetas GROUP BY id_cliente, fecha_de_compra HAVING SUM(importe) > 2000;

Utilizamos la palabra clave HAVING y, después, indicamos la condición para filtrar los resultados. En este caso, solo queremos mostrar a los clientes que, en días concretos, compraron productos por un valor total diario superior a 2.000 dólares.

Ejercicio

Obtener los departamentos en los que el salario promedio en 2022 fue superior a 3.000 €. Mostrar el nombre del departamento junto con el salario promedio.

SELECT departamento, AVG(salario) FROM empleados WHERE fecha = 2022 GROUP BY departamento HAVING AVG(salario) > 3000

Ordenar grupos

Los grupos se pueden ordenar igual que las filas. Echar un vistazo a lo siguiente:

SELECT  id_cliente,  fecha_de_compra,  SUM(importe) FROM tarjetas GROUP BY id_cliente, fecha_de_compra ORDER BY SUM(importe) DESC;

En este caso, ordenamos nuestras filas según el importe total diario de todas las compras de cada cliente. Las filas con el valor más alto aparecerán primero.

Ejercicio

Mostrar las columnas apellido y nombre de la tabla empleados junto con el salario medio de cada persona y el número de años que trabajó en la empresa.

Utilizar los siguientes alias: salario_medio para el salario medio de cada persona y fechas_trabajados para el número de años trabajados en la empresa. Muestre solo los empleados que estuvieron más de 2 años en la empresa. Ordene los resultados según el salario medio, en orden descendente.

SELECT apellido, nombre, AVG(salario) as salario_medio, COUNT(DISTINCT fecha) as fechas_trabajados FROM empleados GROUP BY apellido, nombre HAVING COUNT(DISTINCT fecha) > 2 ORDER BY AVG(salario) DESC;

Alias para tablas

Si queremos seleccionar muchas columnas de dos tablas combinadas, hay que escribir mucho. Todos esos nombres de columnas junto con los nombres de sus tablas. Hay una forma de simplificar la consulta, podemos introducir nuevos nombres temporales (llamados alias) para nuestras tablas:

SELECT  p.campo1,   p.campo2,   p.campo3,   c.campo1,   c.campo2,   c.campo3 FROM tabla1 AS p JOIN tabla2 AS c   ON p.campo1 = c.campo2;

Como se puede ver, en la cláusula FROM, después de los nombres de las tablas utilizamos la palabra clave AS. Esta palabra clave indica que lo que va a continuación se convertirá en el nuevo nombre temporal (alias) de la tabla. Gracias a esto, podemos ahorrarnos un poco de tiempo y escribir nombres más cortos para nuestras tablas.

Usar alias en autocombinaciones

Los alias también son útiles en otras situaciones. Si queremos introducir información sobre los empleados y sus jefes en una base de datos. En un momento dado, también queremos mostrar a los empleados junto con sus jefes, mediante una JOIN. Digamos que almacenamos a los empleados y a los jefes en la misma tabla persona. Todas las filas tienen una columna llamada id_jefe, que contiene el Id del jefe de cada empleado. Podemos combinar la tabla persona con la tabla persona, pero, en SQL no se acepta la siguiente consulta:

persona JOIN persona

Hay que proporcionar dos alias diferentes para la misma tabla de la siguiente manera:

SELECT * FROM persona AS empleado JOIN persona AS jefe   ON empleado.id_jefe = jefe.id;

Gracias a los alias, el motor de la base de datos puede utilizar dos veces la misma tabla (persona), la primera vez para buscar a los empleados y, la segunda, para buscar a sus jefes.

Ejercicio

Queremos saber quién vive en la misma habitación que el alumno Juan García. Utilice la autocombinación para mostrar todas las columnas del alumno Juan García junto con todas las columnas de cada alumno que vive con él en la misma habitación.

Excluir al propio Juan García del resultado.

SELECT * FROM alumno AS alumno1 JOIN alumno AS alumno2     ON alumno1.id_de_habitacion = alumno2.id_de_habitacion WHERE alumno1.nombre = ‘Juan García’ AND alumno1.id <> alumno2.id;

Combinar más tablas

También se puede usar más de una combinación en nuestra consulta de SQL. Digamos que también queremos mostrar toda la información de las habitaciones de los alumnos que viven con Juan García. Lamentablemente, la información del tipo: número de habitación o piso no se almacena en la tabla alumno; necesitamos crear otra combinación con la tabla habitación. Podríamos hacerlo de la siguiente manera:

SELECT * FROM alumno AS s1 JOIN alumno AS s2   ON s1.id_de_habitacion = s2.id_de_habitacion JOIN habitación   ON s2.id_de_habitacion = habitacion.id WHERE s1.nombre = ‘Juan García’ AND s1.nombre != s2.nombre;

Ejercicio

El reto es el siguiente: para cada habitación con 2 camas en la que haya realmente 2 alumnos, queremos mostrar una fila que contenga las siguientes columnas:

El nombre del primer alumno.

El nombre del segundo alumno.

El número de la habitación.

No cambiar ningún nombre de columna. Cada par de alumnos solo debe mostrarse una vez. El alumno cuyo nombre sea el primero en el alfabeto debe mostrarse primero.

Una pequeña pista: en términos de SQL, "primero en el alfabeto" significa "menor que" en los valores de texto.

SELECT   st1.nombre,   st2.nombre,   numero_de_habitacion FROM alumno st1 JOIN alumno st2   ON st1.id_de_habitacion = st2.id_de_habitacion JOIN habitación   ON st1.id_de_habitacion = habitacion.id WHERE st1.nombre < st2.nombre AND camas = 2;