Cómo entender la cláusula GROUP BY

La cláusula GROUP BY es una parte importante de la sentencia SQL SELECT. Pero los nuevos codificadores de SQL pueden encontrarse con algunos problemas cuando esta cláusula se usa incorrectamente. Aquí se explica cómo evitar esos problemas.

Estás aprendiendo SQL. Sabes cómo SELECCIONAR algunos datos DESDE una tabla y cómo filtrar los datos con una cláusula WHERE. Puedes procesar los datos utilizando funciones de agregación (MIN, MAX, SUM, AVG y otras). Pero cuando se trata de una gran cantidad de datos, es posible que necesite reducirlos aún más. Ahí es donde entra en juego la cláusula GROUP BY, que le permite organizar la información en función de los parámetros que establezca.

En este artículo, explicaremos cómo puede utilizar GROUP BY. También hablaremos de un par de posibles problemas y de cómo evitarlos o solucionarlos.

Uso de GROUP BY

Para entender cómo usar GROUP BY, primero necesitamos definir una tabla para practicar. ¿Qué tal una que describa a las personas?

| ID | Name     | Gender | Height | Weight | Eye_color |
--------------------------------------------------------
|  1 | Mark     | Male   |    180 |     78 | Blue      |
|  2 | Susan    | Female |    172 |     59 | Brown     |
|  3 | Thomas   | Male   |    179 |     92 | Brown     |
|  4 | Katarina | Female |    164 |     53 | Green     |
|  5 | Mindy    | Female |    170 |     58 | Blue      |
--------------------------------------------------------

Ya tenemos nuestra tabla de práctica. Ahora entramos en las estadísticas. Por ejemplo, ¿cuál es la altura media de todas nuestras personas? Para averiguarlo, escribimos:

SELECT AVG(Height)
FROM People

+-------------+
| AVG(Height) |
+-------------+
|    173.0000 |
+-------------+

Ahora supongamos que queremos saber la altura media por género. Eso parece bastante fácil; simplemente añadimos una cláusula WHERE. Entonces escribimos:

SELECT AVG(Height)
FROM People
WHERE Gender = ‘Male’

¿Pero qué pasa si introducimos otros géneros en nuestra tabla? En ese caso, tendríamos que escribir consultas adicionales y recoger manualmente los datos que necesitamos. Es más fácil agrupar nuestros datos POR Gender y luego calcular la altura media de cada grupo, como se muestra a continuación.

SELECT Gender, AVG(Height)
FROM People
GROUP BY Gender

+--------+-------------+
| Gender | AVG(Height) |
+--------+-------------+
| Female |    168.6667 |
| Male   |    179.5000 |
+--------+-------------+

Agrupar parece fácil, ¿verdad? Lo único que hay que hacer es añadir la cláusula GROUP BY a la sentencia SQL. Sin embargo, supongamos que necesitamos tener dos parámetros en nuestra búsqueda. En ese caso, tendríamos que agrupar por dos columnas. Digamos que queremos saber cuántos hombres y mujeres tienen los ojos azules, marrones o verdes. Escribiríamos:

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color

+--------+-----------+----------+
| Gender | Eye_color | COUNT(*) |
+--------+-----------+----------+
| Female | Blue      |        1 |
| Female | Brown     |        1 |
| Female | Green     |        1 |
| Male   | Blue      |        1 |
| Male   | Brown     |        1 |
+--------+-----------+----------+

Esto es sólo una introducción a GROUP BY. Puedes utilizarlo de muchas maneras. ¿Por qué no intentas experimentar con diferentes funciones de agregación (como AVG y COUNT) para tener un mejor conocimiento de GROUP BY?

Errores comunes de GROUP BY

Aunque GROUP BY parece bastante fácil de usar, es común que los novatos en SQL se encuentren con algunos mensajes de error confusos. Estos son algunos de los que nos encontramos con bastante frecuencia:

1. Selección de múltiples valores

Lo más importante que hay que recordar cuando se utiliza GROUP BY es que lo que se va a SELECCIONAR debe ser un único valor. Por eso tenemos que utilizar una función agregada: toma múltiples valores y produce uno solo para devolver.

Para que quede un poco más claro, echemos un vistazo a nuestros grupos:

+--------+--------+
| Gender | Height |
+--------+--------+
| Male   |    180 |
|        |    179 |
| Female |    172 |
|        |    164 |
|        |    170 |
+--------+--------+

Cuando pedimos Altura agrupada por Gender, queremos obtener un único valor. Pero aquí Male tiene dos valores de altura y Female tiene tres. ¿Cuál debemos elegir?

SELECT Gender, Height
FROM People
GROUP BY Gender;

(MYSQL) ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.People.Height' which is not functionally dependent on columns 
in GROUP BY clause;

Por eso tenemos que utilizar una función de agregación (como AVG, que hemos utilizado antes) para obtener un único valor.

2. Uso de WHERE para filtrar los resultados

Supongamos que sólo queremos ver los resultados del color de los ojos de los grupos de personas de más de 170 cm de altura. Si intentamos poner eso en la cláusula WHERE, como se muestra a continuación

SELECT Gender, Eye_color, COUNT(*)
FROM People
WHERE AVG(Height) > 170
GROUP BY Gender, Eye_color

Obtendremos el mensaje de error que se muestra a continuación:

(MYSQL) ERROR 1111 (HY000): Invalid use of group function

Eso es porque la base de datos está agrupando nuestros registros después de filtrarlos. Queremos que filtren el resultado de la sentencia GROUP BY. Para ello, utilizamos otra cláusula llamada HAVING. Viene después de GROUP BY y funciona como WHERE. La diferencia es que se pueden utilizar funciones de agregación en ella. Para obtener el conjunto de resultados deseado, escribiríamos este código:

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color
HAVING AVG(Height) > 170

Recuerda que en la base de datos, una sentencia SQL SELECT siempre se ejecuta en este orden:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDENAR POR

Tomamos una fuente de datos, filtramos los registros, los agrupamos, filtramos los grupos, seleccionamos las columnas que queremos y luego los ordenamos.

3. Errores de entidades múltiples

Esto es un poco más avanzado. Cuando hemos unido varias tablas, es muy posible que algunas entidades tengan los mismos nombres. Así que a menudo querremos resultados agrupados por el ID de la entidad en lugar del nombre de la entidad.

Por ejemplo, suponga que está buscando datos de la ciudad de Varsovia. Hay una Varsovia que es la capital de Polonia y una Varsovia que está en el estado de Indiana, Estados Unidos.

Digamos que tenemos una tabla definida así:

Table City:
---------------------
| ID | Name | Award | 
---------------------

Esta tabla describe las ciudades que recibieron uno o más premios. Una ciudad se identifica por su ID y puede tener muchos premios.

Si quisiéramos ver el número de premios recibidos por una ciudad y tener esta información agrupada por el nombre de la ciudad, quizás utilizaríamos esta consulta:

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.Name

El problema es que al utilizar City.Name, se sumarían los premios de Varsovia, Polonia y Varsovia, Indiana. Al fin y al cabo, ¡ambas son Varsovia! Sin embargo, son lugares diferentes y, como tales, tienen valores diferentes en City.ID. Cuando se piensa en una ciudad como entidad de la base de datos, ésta se identifica por su ID y no por sus atributos (como Name). Si agrupamos los resultados por el ID, obtendremos la información correcta. Como todavía queremos mostrar el nombre, usaremos algo como esto:

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.ID

El resultado tendrá entradas separadas para los diferentes Warsaws con los valores que queremos. (Normalmente, habría otra columna, como "país" o "estado", para diferenciar entre esas dos ciudades. Pero para el ejemplo, digamos que no la hay).

4. Uso de valores "no agregados

En el ejemplo anterior, hemos seleccionado el atributo City.Name y hemos agrupado los resultados por el atributo City.ID. En nuestra tabla, todos los registros con el mismo ID tienen también el mismo nombre de ciudad. Algunas bases de datos no tendrán ningún problema con esto y devolverán los resultados esperados, pero otras darán un error diciendo que City.Name no está en la cláusula GROUP BY y que no está agregada. He reproducido el mensaje de error de MySQL a continuación:

(MYSQL) ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.City.Name' which is not functionally dependent on columns 
in GROUP BY clause;

¡Pero es un solo valor! ¿Cómo solucionamos este error? Bueno, ya que un City.ID significa exactamente un City.Name, podemos poner ambos en la cláusula GROUP BY:

SELECT City.Name, COUNT(*)
FROM City
JOIN Person ON (Person.CityID = City.ID)
GROUP BY City.ID, City.Name

Esto debería solucionar el problema.

La práctica hace la perfección

Si todavía no entiendes cómo o cuándo usar la cláusula GROUP BY o si quieres practicar su uso, consulta nuestro curso de Consultas SQL. Cubre todos los aspectos básicos así como los más avanzados, por lo que tendrás muchas oportunidades de perfeccionar tus habilidades SQL.