Como resolver o erro "must appear in the GROUP BY clause or be used in an aggregate function" no PostgreSQL

Aprenda o que causa um dos erros mais comuns do PostgreSQL GROUP BY e como você pode corrigi-lo!

Como um usuário do PostgreSQL, você certamente encontrará muitas mensagens de erro ao escrever um código SQL. Às vezes elas não são muito claras, mas você não precisará de um intérprete para esta: "must appear in the GROUP BY clause or be used in an aggregate function".

Esta mensagem de erro menciona GROUP BY e funções agregadas. Estes são conceitos fundamentais do PostgreSQL que você precisa dominar para corrigir este erro. Naturalmente, corrigi-lo também significa não cometer este erro com muita freqüência quando você passa para conceitos avançados, tais como criar relatórios; usar funções de janela, CTEs e recursividade; escrever suas próprias funções, ou trabalhar com mapas. Tudo isso é coberto em nosso SQL de A a Z em PostgreSQL learning path. Este curso interativo abrangente tornará mais fácil para você evitar mensagens de erro do PostgreSQL. E quando elas aparecerem, você será capaz de resolvê-las rapidamente.

Como você logo verá, resolver o erro que mencionamos no título do artigo também é relativamente fácil. No entanto, requer um entendimento de como o GROUP BY no PostgreSQL funciona.

O que causa o erro "must appear in the GROUP BY clause or be used in an aggregate function"?

Como sempre, vamos ser muito práticos e usar código SQL para mostrar o que causa o erro e como corrigi-lo.

Dataset

O conjunto de dados que vamos utilizar consiste em duas tabelas. A primeira é freelancers:

• id - O ID do freelancer e a chave primária da mesa (PK).
• first_name - O primeiro nome do freelancer.
• last_name - O sobrenome do freelancer.
• email - O e-mail do freelancer.
• country - O país do freelancer.

A consulta CREATE TABLE está disponível aqui.

A segunda tabela é chamada weekly_salary e contém dados sobre o quanto os freelancers são pagos a cada semana. As colunas são:

• id - A identificação do salário e a chave primária da tabela (PK).
• freelancers_id - O ID do freelancer e a chave externa da tabela (FK) dos freelancers da tabela.
• week_start - A data de início para o cálculo do salário.
• week_end - A data final para o cálculo do salário.
• paid_amount - O valor do salário.

Aqui está a consulta para a criação da tabela.

Lançando o erro

Vamos tentar mostrar o primeiro e último nomes dos freelancers e o número de salários semanais que eles receberam até agora:

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;

Nós LEFT JOIN as duas tabelas para que possamos obter todos os dados necessários.

Esses dados são o primeiro e o sobrenome. Depois usamos a função agregada COUNT() na coluna freelancers_id para contar quantas vezes este ID aparece. O número contado é igual ao número de salários que o freelancer recebeu.

Portanto, a contagem dos salários de cada freelancer é...um erro!

O texto desta mensagem do PostgreSQL é bastante claro, e reflete a regra geral: as colunas listadas em SELECT devem aparecer em GROUP BY. Se não aparecerem em GROUP BY, então elas têm que ser utilizadas na função agregada.

Como você pode ver, nossa consulta não tem nada em GROUP BY. Estamos usando a função agregada COUNT() e devemos definir os grupos para agregação, mas não fizemos isso.

Correção do erro "must appear in the GROUP BY clause or be used in an aggregate function".

A abordagem usual para corrigir este erro é simplesmente escrever todas as colunas de SELECT na cláusula GROUP BY. Isto exclui as colunas que são o resultado da função agregada.

Em nosso caso, listar as colunas first_name e last_name em GROUP BY irá corrigir o erro.

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;

A única mudança em relação à consulta anterior é que agora usamos GROUP BY. Nela, escrevemos todas as colunas de SELECT, exceto a utilizada na função agregada. Funções agregadas não são permitidas em GROUP BY - isto mostraria toda uma outra mensagem de erro Postgres.

A consulta retorna a saída acima. É óbvio que realmente corrigimos o erro. Esta saída mostra que Miriam Valetti foi paga uma vez, Claudio Stratos duas vezes, e assim por diante.

Bônus: PostgreSQL Optimizer, ou porque este erro nem sempre aparece

O PostgreSQL utiliza um otimizador. Ele tenta "pensar" e fazer coisas que você quis dizer, mas talvez não tenha escrito explicitamente.

O erro que discutimos não aparecerá no PostgreSQL se você agrupar pela chave primária.

Dê uma olhada nesta 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;

Ele tenta devolver o ID do freelancers, nome, sobrenome e o valor total do salário pago até o momento (a função SUM() ). As duas tabelas são LEFT JOINed e os dados são filtrados para mostrar apenas os freelancers da Itália.

O que parece estranho é o GROUP BY. Não acabamos de dizer que todas as colunas em SELECT também devem aparecer em GROUP BY?

Este código deve retornar um erro. Vamos executá-lo e ver:

Surpreendentemente, a consulta não retorna um erro! Por que isso acontece?

O otimizador PostgreSQL permite que você agrupe por chave primária (PK) e tenha as colunas não-PK em SELECT. O PostgreSQL entende que as colunas PK determinam os valores de outras colunas na mesma tabela.

Em nosso caso, a coluna f.id é a PK. É suficiente tê-la em GROUP BY; não há necessidade de incluir first_name e last_name (as colunas não-PK) em GROUP BY.

Entretanto, você não deve confiar nisso - o otimizador não é tão inteligente assim! Por exemplo, ele não pode identificar PKs para subconsultas, CTEs, e vistas.

Vamos reescrever a consulta acima com uma subconsulta. A tabela que você usa pode ser muito maior, então você pode querer filtrar apenas os freelancers da Itália imediatamente. Você fará isso em uma 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;

A parte SELECT é a mesma de antes. Mas em vez de usar o todo freelancers mesa e unindo-a com weekly_salaryescrevemos o ifr (como em 'italy_freelancers') subquery.

Esta subconsulta seleciona todas as colunas da tabela freelancers e filtra os dados por país em WHERE.

Como usamos a subconsulta em vez do todo freelancers agrupamos a produção pela coluna id da subconsulta.

Isto deve funcionar, pois a subconsulta é basicamente apenas uma versão limitada da freelancers mesa. No entanto, o PostgreSQL retorna um erro familiar:

Utilizamos esta tabela derivada como uma subquisição, portanto o otimizador do PostgreSQL não reconhece seu PK. Se você insiste em ter uma subconsulta, então este erro é corrigido da mesma forma que antes: liste todas as outras colunas de SELECT em 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;

A consulta agora funciona:

Nós sobre erros aprendidos corrigidos. Agora é hora de evitá-los.

Obter tais mensagens de erro no PostgreSQL é muito útil, pois eles fazem você aprender. É claro, mesmo os usuários mais avançados do PostgreSQL verão este (ou qualquer outro) erro, então você não irá evitá-los completamente.

É importante que você saiba como corrigir os erros. Entretanto, o objetivo é ver este GRUPO POR erro tão raramente quanto possível. É claro que, às vezes, o otimizador PostgreSQL pode salvá-lo. É uma boa ferramenta para se ter, mas você deve confiar mais em seu conhecimento do que no fato de o otimizador ser capaz de ler sua mente. Para garantir isso, você precisa de muita prática para fazer escrever as colunas no SELECT e depois no GROUP BY automaticamente.

O SQL de A a Z no PostgreSQL irá aumentar seu conhecimento e confiança nele.

Há também algumas idéias adicionais sobre como praticar o PostgreSQL online. Use isto para se tornar seu próprio otimizador de código!