Cómo Resolver el Error "must appear in the GROUP BY clause or be used in an aggregate function" en PostgreSQL

Aprenda qué causa uno de los errores GROUP BY más comunes en PostgreSQL y cómo puede solucionarlo.

Como usuario de PostgreSQL, seguramente se encuentra con muchos mensajes de error cuando escribe un código SQL. A veces son bastante confusos, pero no necesitará un intérprete para este: "must appear in the GROUP BY clause or be used in an aggregate function".

Este mensaje de error menciona GROUP BY y funciones agregadas. Estos son conceptos fundamentales de PostgreSQL que necesita dominar para solucionar este error. Por supuesto, solucionarlo también significa no cometer este error con demasiada frecuencia cuando pase a conceptos avanzados, como la creación de informes; el uso de funciones de ventana, CTE y recursividad; la escritura de sus propias funciones o el trabajo con mapas. Todo esto está cubierto en nuestra ruta de aprendizaje Curso completo de SQL en PostgreSQL. Este completo curso interactivo le facilitará evitar los mensajes de error de PostgreSQL. Y cuando aparezcan, podrá resolverlos rápidamente.

Como pronto verá, resolver el error que mencionamos en el título del artículo también es relativamente fácil. Sin embargo, requiere una comprensión de cómo funciona GROUP BY en PostgreSQL.

¿Qué causa el error "must appear in the GROUP BY clause or be used in an aggregate function"?

Como siempre, seremos muy prácticos y usaremos código SQL para mostrarle qué causa el error y cómo solucionarlo.

Conjunto de datos

El conjunto de datos que utilizaremos consta de dos tablas. La primera es freelancers:

• id - El ID del autónomo y la clave primaria (PK) de la tabla.
• first_name - El nombre del autónomo.
• last_name - El apellido del freelancer.
• email - El email del freelancer.
• country - El país del freelancer.

La consulta CREATE TABLE está disponible aquí.

La segunda tabla se llama weekly_salary y contiene datos sobre la remuneración semanal de los autónomos. Las columnas son:

• id - El ID del salario y la clave primaria de la tabla (PK).
• freelancers_id - El ID del autónomo y la clave externa (FK) de la tabla autónomos.
• week_start - La fecha de inicio del cálculo del salario.
• week_end - La fecha final para el cálculo del salario.
• paid_amount - El importe del salario.

Esta es la consulta para crear la tabla.

Lanzamiento del error

Intentemos mostrar los nombres y apellidos de los autónomos y el número de salarios semanales que han recibido hasta el momento:

SELECT
  first_name, 
  last_name,
  COUNT(freelancers_id) AS number_of_payments
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id;

LEFT JOIN las dos tablas para poder obtener todos los datos necesarios.

Esos datos son el nombre y los apellidos. A continuación, utilizamos la función agregada COUNT() en la columna freelancers_id para contar cuántas veces aparece este ID. El número contado es igual al número de salarios que recibió el autónomo.

Por tanto, el recuento de salarios de cada autónomo es... ¡un error!

La redacción de este mensaje PostgreSQL es bastante clara, y refleja la regla general: las columnas listadas en SELECT deben aparecer en GROUP BY. Si no aparecen en GROUP BY, entonces tienen que ser utilizadas en la función de agregado.

Como puede ver, nuestra consulta no tiene GROUP BY en absoluto. Estamos utilizando la función de agregación COUNT() y deberíamos definir los grupos para la agregación, pero no lo hemos hecho.

Solución del error "must appear in the GROUP BY clause or be used in an aggregate function"

El enfoque habitual para solucionar este error es simplemente escribir todas las columnas de SELECT en la cláusula GROUP BY. Esto excluye las columnas que son el resultado de la función agregada.

En nuestro caso, la inclusión de las columnas first_name y last_name en GROUP BY solucionará el error.

SELECT 
  first_name, 
  last_name,
  COUNT(freelancers_id) AS number_of_payments
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id
GROUP BY first_name, last_name;

El único cambio respecto a la consulta anterior es que ahora utilizamos GROUP BY. En ella, escribimos todas las columnas de SELECT excepto la utilizada en la función de agregado. Las funciones agregadas no están permitidas en GROUP BY - esto mostraría otro mensaje de error de Postgres.

La consulta devuelve la salida anterior. Es obvio que realmente hemos corregido el error. Este resultado muestra que Miriam Valetti cobró una vez, Claudio Stratos dos veces, y así sucesivamente.

Bonificación: Optimizador PostgreSQL, o por qué este error no siempre aparece

PostgreSQL utiliza un optimizador. Trata de "pensar" y hacer cosas que usted quiso decir pero que tal vez no escribió explícitamente.

El error que discutimos no aparecerá en PostgreSQL si usted agrupa por la clave primaria.

Eche un vistazo a esta consulta:

SELECT 
  f.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM freelancers f
LEFT JOIN weekly_salary ws
ON f.id = ws.freelancers_id
WHERE country = 'Italy'
GROUP BY f.id;

Intenta devolver el ID de los autónomos, su nombre, apellidos y el importe total del salario que se les ha pagado hasta el momento (la función SUM() ). Las dos tablas son LEFT JOINed y los datos se filtran para mostrar sólo autónomos de Italia.

Lo que parece extraño es GROUP BY. ¿No acabamos de decir que todas las columnas de SELECT deben aparecer también en GROUP BY?

Este código debería devolver un error. Ejecutémoslo y veamos:

Sorprendentemente, ¡la consulta no devuelve ningún error! ¿A qué se debe?

El optimizador de PostgreSQL le permite agrupar por clave primaria (PK) y tener las columnas no PK en SELECT. PostgreSQL entiende que las columnas PK determinan los valores de otras columnas en la misma tabla.

En nuestro caso, la columna f.id es la PK. Con tenerla en GROUP BY es suficiente; no es necesario incluir first_name y last_name (las columnas no PK) en GROUP BY.

Sin embargo, no hay que fiarse: el optimizador no es tan inteligente. Por ejemplo, no puede identificar PKs para subconsultas, CTEs y vistas.

Reescribamos la consulta anterior con una subconsulta. La tabla que utilice puede ser mucho más grande, por lo que es posible que desee filtrar inmediatamente sólo los autónomos de Italia. Esto lo harás en una subconsulta:

SELECT 
  ifr.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM (SELECT *
	FROM freelancers
	WHERE country = 'Italy') AS ifr 
LEFT JOIN weekly_salary ws
ON ifr.id = ws.freelancers_id
GROUP BY ifr.id;

La parte SELECT es la misma que antes. Pero en lugar de utilizar toda la tabla freelancers y unirla con weekly_salaryescribimos ifr (como en 'italy_freelancers').

Esta subconsulta selecciona todas las columnas de la tabla freelancers y filtra los datos por país en WHERE.

Como utilizamos la subconsulta en lugar de toda la tabla freelancers tabla, agrupamos la salida por la columna id de la subconsulta.

Esto debería funcionar, ya que la subconsulta es básicamente una versión limitada de la tabla freelancers de la tabla. Sin embargo, PostgreSQL devuelve un error familiar:

Usamos esta tabla derivada como una subconsulta, por lo que el optimizador PostgreSQL no reconoce su PK. Si insiste en tener una subconsulta, entonces este error se soluciona de la misma manera que antes: liste todas las demás columnas de SELECT en GROUP BY.

SELECT 
  ifr.id,
  first_name, 
  last_name,
  SUM(paid_amount) AS total_paid_amount
FROM (SELECT *
	FROM freelancers
	WHERE country = 'Italy') AS ifr 
LEFT JOIN weekly_salary ws
ON ifr.id = ws.freelancers_id
GROUP BY ifr.id, first_name, last_name;

La consulta ahora funciona:

Ya hemos aprendido a corregir errores. Ahora es el momento de evitarlos.

Recibir este tipo de mensajes de error en PostgreSQL es muy útil, ya que te hacen aprender. Por supuesto, incluso los usuarios más avanzados de PostgreSQL verán de vez en cuando este (o cualquier otro) error, así que no los evitará completamente.

Es importante que sepa cómo corregir los errores. Sin embargo, el objetivo es ver este error GROUP BY lo menos posible. Por supuesto, algunas veces el optimizador PostgreSQL puede salvarlo. Es una buena herramienta para tener, pero debe confiar más en su conocimiento que en que el optimizador sea capaz de leer su mente. Para asegurarte de eso, necesitas mucha práctica para que escribir las columnas en SELECT y luego en GROUP BY sea automático.

El Curso completo de SQL en PostgreSQL aumentará tu conocimiento y confianza en ello.

También hay algunas ideas adicionales sobre cómo practicar PostgreSQL en línea. ¡Usa esto para convertirte en tu propio optimizador de código!