7 errores comunes de GROUP BY

¿Estás cometiendo estos errores con GROUP BY en SQL? Descubra cuáles son, cómo evitarlos y cómo solucionarlos.

La sentencia GROUP BY de SQL puede revelar rápidamente una poderosa visión de los datos. Al principio, el uso de GROUP BY puede parecer sencillo, por ejemplo, al crear informes SQL básicos que se presentarán a los responsables de la toma de decisiones empresariales. Pero mientras aprende esta poderosa función, podría quedar atrapado en errores extraños u obtener resultados incorrectos causados por sentencias GROUP BY mal escritas. Si sientes que las cosas no cuadran con tu uso de GROUP BY, sigue leyendo. En este artículo, explicaré los errores más comunes de GROUP BY y cómo puede evitarlos.

Deja de cometer estos 7 errores comunes de GROUP BY

1. Olvidar GROUP BY con funciones agregadas

Usted utiliza las sentencias SELECT con la cláusula GROUP BY cuando desea agrupar y organizar las filas en grupos específicos y luego realizar un cálculo específico de cada grupo.

El error más común de GROUP BY es olvidarse de escribir GROUP BY dentro de la sentencia SELECT.

He aquí un ejemplo. Imagine que tiene la tabla recetasque contiene 100 registros y seis columnas. Esta tabla almacena el número de vistas (no_of_views) por cada receta publicada en un famoso sitio web culinario:

tabla de recetas

A continuación, una breve descripción de las columnas de la tabla:

• meal_category - La categoría de la receta (sopa, bebidas, postres, etc.).
• name - El nombre de la receta.
• author - El nombre del autor.
• no_of_views - El número de visualizaciones (total de páginas/recetas vistas) en el mes en curso.
• no_of_views_lst_mth - El número de visitas (total de páginas/recetas vistas) del mes anterior.
• author_id - El número de identificación único del autor.

Digamos que quiere contar el número de recetas en cada categoría de comida. Si escribe la sentencia así (sin GROUP BY al final) ...

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes ;

... la mayoría de los motores SQL le darán un error. Algunos motores, sin embargo, darán resultados extraños y no deseados. Estoy usando MySQL y cuando ejecuto esta sentencia, obtengo lo siguiente

Resultado sin GROUP BY

100 es el recuento total de todas las recetas del conjunto de datos y la categoría de comida "Aperitivo frío" es sólo una categoría de las diez. Para corregir este tipo de error, es necesario añadir un GROUP BY meal_category al final de la sentencia. (De lo contrario, su resultado en MySQL no tiene sentido).

El SELECT correcto tiene este aspecto:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

He aquí una breve explicación de lo que ocurre:

• Los registros se combinan según la categoría de la comida. Por ejemplo, los postres son un grupo, las sopas otro, los platos principales otro, etc. La columna meal_category se especifica después de GROUP BY; también aparece en SELECT.
• Para cada grupo, utilizamos COUNT(*) para contar el número total de recetas en ese grupo.

No voy a profundizar en la sintaxis aquí, pero definitivamente te sugiero que leas Explicación de GROUP BY en SQL o Uso de GROUP BY en SQL para más detalles.

Como puedes ver, el resultado es el esperado:

Resultado válido de GROUP BY

2. Confundiendo WHERE y HAVING

Tal vez quiera ver sólo las categorías de comidas que tienen más de 10 recetas. Muchos principiantes escribirían esta consulta:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

Esta sentencia devolverá un error porque no se pueden utilizar funciones agregadas en una cláusula WHERE. WHERE se utiliza con GROUP BY cuando se desea filtrar filas antes de agruparlas.

En nuestro ejemplo, queremos filtrar filas después de agruparlas; en casos como éste, necesitamos utilizar la cláusula HAVING:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

Este malentendido sobre la diferencia entre HAVING y WHERE es el segundo error más común con GROUP BY.

Vamos a aclarar esta diferencia con dos ejemplos más.

Ejemplo 1 - Cómo mostrar las categorías de comidas con más de 1M de vistas

Una sentencia que muestre sólo las categorías con más de 1 millón de visitas totales a la página puede escribirse así:

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

Aquí estamos usando HAVING porque queremos filtrar los registros después de haberlos agrupado. El resultado se presenta a continuación:

Ejemplo con HAVING

Ejemplo 2 - Rendimiento de Juan en cada categoría de comida

Esta consulta extrae sólo las recetas de Juan y calcula su rendimiento:

SELECT 
  meal_category, 
  sum(no_of_views) AS total 
FROM recipes 
WHERE author = ‘John’ 
GROUP BY meal_category;

Estamos usando WHERE porque necesitamos filtrar los registros (para que sólo obtengamos los datos de Juan) antes de poner los registros en grupos por categoría de comidas. El resultado es el siguiente:

Los KPI de John

HAVING y WHERE están bien descritos en nuestros artículos ¿Cuál es la diferencia entre las cláusulas WHERE y HAVING en SQL? y 5 ejemplos de GROUP BY. Si quieres ver más ejemplos sobre este tema, te sugiero que empieces por ahí.

3. Listando una Columna dentro de SELECT pero no en GROUP BY

Ahora suponga que quiere ver el número total de vistas por meal_category y author. Podemos hacerlo... sólo tenemos que añadir la columna autor a nuestra consulta anterior:

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

¿Le parece bien? Por supuesto que no; arrojará un error en la mayoría de los motores SQL. Por ejemplo, Oracle le dirá "error: No es una expresión GROUP BY". ¿Por qué este error tan confuso? ¿Qué falta aquí?

Pues bien, el motor SQL no sabe cómo calcular el total de cada autor porque no lo hemos incluido en la cláusula GROUP BY; el atributo autor no aparece dentro de la cláusula GROUP BY. Este es otro error común con GROUP BY.

Arreglemos esta consulta y ejecutémosla una vez más:

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

El resultado es:

Ahora esto parece estar bien. Recuerde que las columnas no agregadas que aparecen en SELECT también deben aparecer en GROUP BYEn nuestro caso, las columnas no agregadas son meal_category y author, que ahora están en SELECT y GROUP BY.

No se enumeran las columnas que están dentro de las funciones agregadas en GROUP BY. En nuestro ejemplo, la columna no_of_views se utiliza en la función agregada SUM() y, por lo tanto, no aparece en la cláusula GROUP BY.

Si desea obtener más información sobre este tema, consulte nuestro artículo Cláusula GROUP BY: ¿Qué tan bien la conoce? En él se explica por qué las columnas seleccionadas deben aparecer en la cláusula GROUP BY. También, Cómo solucionar un error de 'No es una expresión GROUP BY ' da más ejemplos relacionados con este tipo de error.

4. No agrupar por una clave única

Ahora vamos a intentar algo más. Supongamos que queremos obtener el promedio de páginas vistas de cada autor de recetas. La siguiente consulta calcula la media del número total de páginas vistas de cada autor utilizando el nombre del autor:

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

Al ver el resultado, se observa que Lisa tiene una media de 116101,5 páginas vistas:

GROUP BY autor - pero los nombres no son únicos

Sin embargo, en realidad tenemos dos autores llamados Lisa en nuestra tabla. Cuando agrupamos los resultados por la columna de autor, las dos Lisas se promedian juntas. ¿Por qué? Porque estamos utilizando una columna no única en GROUP BY. Esto significa que no todos los valores de agrupación tienen que ser únicos. Si queremos ver la media de cada Lisa por separado, deberíamos añadir author_id (una columna única) a la lista GROUP BY:

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

Ahora vemos cómo las recetas de Lisa(id=11) son mucho más vistas que las recetas de Lisa(id=5):

GROUP BY con author y author_id

Es importante pensar siempre en las claves de agrupación. Los valores de agrupación deben ser únicos y deben representar a cada grupo de la forma deseada. De lo contrario, obtendrás resultados inexactos y confusos y posiblemente un error de GROUP BY.

5. Confundir COUNT(distinct) y COUNT(*)

Si tiene curiosidad por ver el número total de autores de cada categoría de comida, puede escribir una sentencia GROUP BY para calcularlo. Utilicemos COUNT(*) y recuperemos el número de autores de cada categoría:

SELECT 
  meal_category, 
  count(*) 
FROM recipes 
GROUP BY meal_category;

Este es el resultado - pero no es lo que esperabas, ¿verdad?

Se trata del número total de recetas en cada categoría, no del número total de autores. ¿A qué se debe esto? Bueno, COUNT(*) cuenta todas las filas de cada grupo. La tabla receta contiene información en un recipe nivel - cada registro es una receta. Esta consulta cuenta las recetas (filas) de cada categoría, no los autores de las recetas.

Un autor puede tener muchas recetas en cada categoría, así que para obtener la información que quieres, debes contar los autores distintos (usando COUNT(distinct author) en lugar de COUNT(*)) dentro de cada grupo. Este es un error muy común de GROUP BY.

Entonces, ¿cuándo debe utilizar COUNT(*), COUNT(expression) y COUNT(distinct expression)?

Veamos un ejemplo:

SELECT 
  meal_category, 
  count(distinct author), 
  count(author),
  count(*) 
FROM recipes 
GROUP BY meal_category;

La diferencia entre COUNT(*) y COUNT(expression) es visible si estamos haciendo cálculos en una columna que tiene algunos valores perdidos. Cuando hay valores perdidos, COUNT(*) contará todos los registros de un grupo y COUNT(expression) contará sólo los valores no nulos.

En el ejemplo anterior, COUNT(*) y COUNT(author) dan exactamente el mismo resultado porque la columna autor no tiene valores NULL.

COUNT(distinct author) nos da el número de autores distintos para cada categoría, que no es el mismo que COUNT(*). Por ejemplo, la categoría de comida fría de aperitivo contiene seis recetas de dos autores distintos. COUNT(*) cuenta el número de recetas (registros) en cada categoría, mientras que COUNT(distinct author) cuenta el número de autores distintos.

Por lo tanto, si desea mostrar el número total de autores distintos por cada categoría de comida, utilice COUNT(distinct author). Esta es la consulta correcta:

SELECT 
  meal_category, 
  count(distinct author)
FROM recipes 
GROUP BY meal_category;
GROUP BY meal_category;

Para una explicación más detallada, consulte ¿Cuál es la diferencia entre COUNT(*), COUNT(1), COUNT(nombre de columna) y COUNT(nombre de columna DISTINTO)?

6. Problemas al utilizar funciones agregadas con NULLs

Este es otro problema de "valores perdidos". Supongamos que quiere calcular el promedio del número total de vistas del mes anterior para cada categoría. Su colega ha calculado esas cifras, pero le gustaría que usted comprobara el resultado.

Esta es su consulta:

SELECT
      meal_category,
      avg(no_of_views_lst_mth) as average,
  FROM recipes 
GROUP BY meal_category;

Y lo que obtienes es...

El resultado parece correcto y usted está seguro de que su consulta es correcta. Sin embargo, tu colega ha obtenido cifras ligeramente diferentes:

¿Qué ha pasado? ¿Por qué los resultados son diferentes?

En pocas palabras, los distintos resultados se deben a las diferentes interpretaciones de los valores que faltan.

La columna no_of_views_lst_mth representa el número de páginas vistas totales en el mes anterior. Si una receta se creó en el mes actual, esta columna será NULA para esa fila.

Por ejemplo, la receta Tarta de queso y plátano de Ally se escribió en el mes actual, por lo que no hay estadísticas para el mes anterior:

La tarta de queso con plátano se publicó en el mes actual

Ahora, volvamos a los promedios y sus diferentes resultados. Los promedios se calculan como la suma total de no_of_views_lst_mth dividida por el número total de registros. Si utiliza la función AVG() y hay NULLs presentes, el motor simplemente ignora los NULLs y hace los cálculos sin ellos. Esto es lo que ocurrió cuando ejecutó su consulta: los NULL se omitieron. En algunos casos, querrá reemplazar los NULLs por 0 (porque la lógica del negocio lo dicta); esto es lo que hizo su colega, que produjo cifras ligeramente diferentes. Aquí está la consulta de su colega:

SELECT
      meal_category,
           avg(CASE WHEN no_of_views_lst_mth is null 
            THEN 0 
            ELSE no_of_views_lst_mth END) AS average
FROM recipes 
GROUP BY meal_category;

Observe cómo los promedios de estas dos consultas tratan los NULL de forma diferente. Por ejemplo, la categoría "postres" contiene NULLs. Por lo tanto, la primera consulta omite estas filas y no las cuenta en el número total de filas; esto da el valor 144349,72. La segunda consulta sustituye todos los NULLs por cero y cuenta estas filas en la media, dando un valor menor de 129914,75.

Yo diría que ambas consultas podrían ser válidas, dependiendo de cómo se quieran calcular los promedios.

7. Usando COUNT(*) con GROUP BY y un LEFT JOIN

El uso de GROUP BY con una sentencia LEFT JOIN puede ser bastante confuso - especialmente con COUNT(). Veamos cómo funcionan COUNT(*) y COUNT(expression) en una sentencia LEFT JOIN.

Supongamos que alguien de marketing tiene la siguiente tabla, recipes_campaigns. Contiene información sobre el número de campañas realizadas en cada categoría de comida en el mes actual:

campaña_recetas

Además de los datos en recipes_campaigns, el responsable de marketing también quiere ver el número de recetas de cada categoría de comidas. Para ello, necesitaremos la información de la tabla recipes tabla. Así que unamos estas dos tablas por la izquierda y calculemos el número de recetas usando COUNT(*), así:

SELECT 
      a.meal_category,
      count(*),
     FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Este es el resultado:

Esto no es lo que esperábamos. La tabla recipe no contiene ninguna receta en la categoría 'brunch', así que ¿por qué entonces obtuvimos ese 1 en el resultado? ¡Esto sucede porque COUNT() se aplica al resultado de LEFT JOIN! Cuando LEFT JOIN dos tablas, la categoría 'brunch' estará presente en el resultado - incluso si no hay recetas o categorías que coincidan en la tabla recipe tabla.

¿Cómo podemos arreglar esto? Si usamos COUNT(expression) en lugar de COUNT(*), obtendremos el resultado que queremos:

SELECT 
      a.meal_category,
      count(author_id),
FROM recipes_campaigns a 
LEFT JOIN recipes b ON a.meal_category=b.meal_category
GROUP BY a.meal_category;

Una vez que se ejecuta esto, se obtiene:

Aquí, COUNT(author_id) cuenta sólo los valores no NULL en author_id después de realizar el LEFT JOIN. No hay ningún valor en author_id para la categoría 'brunch'; en otras palabras, es NULL y el resultado para esa categoría es 0.

Puede resolver los errores de GROUP BY

A través de varios ejemplos, hemos explorado GROUP BY y los errores más comunes que los principiantes suelen cometer. Espero que ahora tenga una mejor idea de cómo funciona GROUP BY y qué es lo que causa esos errores extraños o resultados confusos.

GROUP BY es realmente muy importante en la creación de informes. Si quiere aprender a construir buenos informes, le recomiendo nuestro Cómo crear informes básicos con SQL curso. Tiene muchos ejercicios interactivos que le permiten ganar experiencia escribiendo GROUP BY consultas para los informes. Y una mayor experiencia reduce sin duda la posibilidad de cometer errores.