Una de las cosas buenas de escribir es que cuando uno se motiva a hacerlo sobre un tema que no conoce, le toca aprender y entenderlo para poder explicarlo con propiedad o desde una nueva óptica.
Este tema que vengo a compartir no lo entiendo ni un poquito. Llevo mis buenos años trabajando haciendo software pero el error:
ERROR: column “MY_TABLE.MY_COLUMN” must appear in the GROUP BY clause
or be used in an aggregate function
Nunca lo he entendido de tal forma que sepa qué está pasando (y eventualmente prevenga que ocurra) cuando escribo consultas SQL.
Puede que me equivoque en lo que explique pero haré el intento de entenderlo y explicarlo lo mejor que pueda. ¡Empecemos!
¿Qué es el GROUP BY de SQL?
Bueno, la definición formal es bastante «pesada» ya que habla como en términos algebraicos(?) así que trataré de dar una versión más suave y amena.
La sentencia GROUP BY permite agrupar registros que tienen un mismo valor en común en filas de estilo resumen. Generalmente, se usará GROUP BY en conjunto con funciones de agregación (ejemplo, COUNT, MIN, MAX, etc) para agrupar el resultado final por las columnas que se indicadas.
Hasta ahí todo normal. Es la típica descripción que podríamos encontrar en Internet.
Quiero agregar algunos datos interesantes que hallé sobre la clausula GROUP BY.
- La instrucción GROUP BY no garantiza el orden de los registros. Para hacerlo, se recomienda usar una instrucción ORDER BY con las mismas columnas indicadas en el GROUP BY.
- Si se quieren aplicar condiciones a la operación del GROUP BY no se usa WHERE sino la clausula HAVING.
Bueno, ahora pasemos a las funciones de agregación ya que son un tema crucial para poder entender y resolver el error.
Funciones de Agregación
En SQL, las funciones nos ayudan a hacer operaciones en un conjunto de resultados para devolver un único valor agregado para todos ellos. O sea, de un grupo de datos, una función de agregación permite saber la media, el mayor, el menor, cantidades, etc.
Bastante sencillo por ahí. El mejor ejemplo de función de agregación es la sentencia COUNT la cual usamos para saber la cantidad de registros en una tabla. Los registros de la tabla son el conjunto de resultados y el valor devuelto por COUNT es el agregado de la suma de cada registro.
El Error
Veamos de nuevo el error:
ERROR: column “MY_TABLE.MY_COLUMN” must appear in the GROUP BY clause
or be used in an aggregate function
Si somos afortunados, el motor de base de datos nos dirá claramente lo que está pasando con el error al usar GROUP BY (hay otros motores que no son tan dicientes).
La mayor confusión que puede producir este error es que normalmente cuando escribimos nuestras consultas y sabemos que necesitamos agrupar los resultados por una columna, pues normalmente pensamos en términos de esa sola columna.
Partamos de que tenemos una tabla llamada films :
SELECT id, genre, title
FROM films;
id | genre | title
----+----------+-------------------
1 | thriller | Blade Runner 2049
2 | thriller | Inception
3 | scifi | Arrival
y una reviews :
SELECT id, film_id, content
FROM reviews;
id | film_id | content
----+---------+-----------------------------------------------------------
1 | 1 | Visually stunning and thought provoking, but not flawless
2 | 1 | One of the best sequels of all time
3 | 1 | Strangely boring, lacking tension and intelligence
4 | 2 | Amazing Directing, Captivating Plot, Overall Great
Y queremos saber cuántas películas hay por género:
SELECT genre, COUNT(*)
FROM films
GROUP BY genre;
genre | count
----------+-------
thriller | 2
scifi | 1
Cuando queremos saber qué título está en cada grupo de géneros:
SELECT title, genre, COUNT(*)
FROM films
GROUP BY genre;
ERROR: column "films.title" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT title, genre, count(*) FROM films GROUP BY genre;
Hemos encontrado el error.
Pasa que el motor de base de datos no sabe que title escoger ya que podría haber más de uno con el mismo género (genre).
Si nos guiamos del mensaje de error nos podríamos equivocar sino entendemos qué es lo que en verdad pasa. Por eso, para solucionar este error y/o prevenirlo debemos entender la diferencia entre la primera parte del mensaje:
must appear in the GROUP BY clause
o la segunda parte:
be used in an aggregate function
Casi siempre la más sencilla y pronta es agregar las columnas que aparecen en el SELECT en la instrucción GROUP BY pero de esa forma podemos obtener resultados que no son adecuados.
Si a la consulta pasada le agregamos title al GROUP BY, pasa esto:
SELECT title, genre, COUNT(*)
FROM films
GROUP BY title, genre;
title | genre | count
-------------------+----------+-------
Blade Runner 2049 | thriller | 1
Inception | thriller | 1
Arrival | scifi | 1
No hicimos nada.
Como no funcionó al intentar resolver según la primera parte del mensaje de error, debemos probar con la segunda parte del mensaje la cual es usando una función de agregación.
Si queremos ver qué películas están siendo tenidas en cuenta al agrupar, hay que usar una función de agregación en la columna title.
SELECT string_agg(title, ', ') AS titles, genre, COUNT(*)
FROM films
GROUP BY genre;
titles | genre | count
------------------------------+----------+-------
Blade Runner 2049, Inception | thriller | 2
Arrival | scifi | 1
Para así obtener el resultado que en verdad buscamos.
Ya vimos que es importante aprender a diferenciar entre las dos formas de resolver el error. Ahora trataré de explicar el trasfondo.
¿Qué dice el estándar sobre GROUP BY?
Según el artículo «Debunking GROUP BY myths«, hay un mito alrededor de esta sentencia el cual hace creer que el estándar de SQL obliga a que las columnas presentes en el SELECT deben estar en el GROUP BY si estas no están en una función de agregación.
Cuando se analiza la definición del estándar del año 1992, la jerga es bastante técnica y toca interpretar. Según el artículo, la interpretación adecuada debería ser:
- Las consultas que usen GROUP BY solo podrán tener referencias a una columna en la instrucción SELECT si esa columna está listada en la clausula GROUP BY o si es parte de una función de agregación.
En las versiones de 1999 y 2003, dejó de ser obligatorio que las columnas no agregadas presentes en el SELECT también debían estarlo en el GROUP BY.
En su lugar, se requiere que cada columna no agregada usada en el SELECT sea funcionalmente dependiente con respecto a la clausula GROUP BY.
Dependencias Funcionales
Y así llegamos al punto más álgido de este artículo. Una vez entendido este punto, el error descrito en este artículo podrá ser manejado de mejor forma cada vez que escribamos consultas SQL.
En el artículo de apoyo, se menciona que la definición que el estándar de SQL da para Dependencias Funcionales es bastante extenso y complejo, sin embargo, hay una forma sencilla de entenderlo.
mysql> SELECT @A:=1 AS A
-> , @B:=@A + 1 AS B;
+---+------+
| A | B |
+---+------+
| 1 | 2 |
+---+------+
El ejemplo anterior nos muestra como B es funcionalmente dependiente de A. El valor de B se obtiene al realizar una operación puntual con respecto al valor de A.
Otra forma de entender este concepto de Dependencia Funcional es cuando se mira desde la Normalización.
Normalizar requiere que se determine una columna(grupo de columnas) que será llave de la tabla. Dicha llave será la forma de identificar un registro único. Por esto, si una columna es llave, todo el resto de columnas que no sean una llave serán funcionalmente dependientes de la llave.
Bueno, todo bien pero ¿cómo se relaciona GROUP BY con Dependencias Funcionales? Como vimos antes, el estándar de 1999 y 2003 ahora pide que las columnas del SELECT sean funcionalmente dependientes a las que aparecen en el GROUP BY.
Por lo anterior, podríamos inferir que si sabemos que una columna solo tiene un valor para cualquier combinación de los valores de las columnas que se listan en el GROUP BY, dicha columna la podemos poner en el SELECT sin escribirla en el GROUP BY y sin necesidad de usar una función de agregación.
Conclusiones
En definitiva, es un problema que se vuelve sencillo con la experiencia y la práctica. No hay una solución única pero si revisamos con detalle la consulta que escribimos, el error será más fácil de corregir.
Por un lado, el error se puede corregir teniendo en cuenta que las columnas de la consulta mencionadas en el GROUP BY también estén presentes en el SELECT.
Pero no siempre será de esa forma y probablemente necesitemos una función de agregación para obtener un valor resumido para esa columna problemática.
Finalmente, vemos como el concepto de Dependencia Funcional es un guía para trabajar junto con GROUP BY.
Enlaces de Apoyo
Este artículo lo pude escribir luego de leer sobre GROUP BY en estos sitios:
- GROUP BY según Campus MVP
- GROUP BY según W3Schools
- Artículo que habla sobre el error y me inspiró a escribirlo en Español
- Artículo que habla sobre el error a fondo. Recomendado
Otro Espacio Blog 