SQL SERVER – Error: 147 An aggregate may not appear in the WHERE clause

Começar a programar - seja em Python, MongoDB, R ou SQL - sempre envolverá desafios em relação a problemas de sintaxe ou mesmo alguma lógica particular da linguagem. Este erro no SQL Server é relativamente comum e hoje trazemos uma explicação do porque ele aparece e como resolvê-lo.

Vamos utilizar a tabela abaixo (TEAMSTATS) como exemplo:

NAME      POS  AB HITS WALKS SINGLES DOUBLES TRIPLES HR SO
--------- --- --- ---- ----- ------- ------- ------- -- --
JONES      1B 145   45 34    31       8       1       5 10
DONKNOW    3B 175   65 23    50      10       1       4 15
WORLEY     LF 157   49 15    35       8       3       3 16
DAVID      OF 187   70 24    48       4       0      17 42
HAMHOCKER  3B  50   12 10    10       2       0       0 13
CASEY      DH   1    0  0     0       0       0       0  1

E precisamos encontrar os jogadores que possuam o número de HITS (rebatidas) igual ao valor máximo de HITS. A função MAX() retornará o valor máximo de uma coluna. Assim, temos

SELECT MAX(HITS)
FROM TEAMSTATS

que nos retornará o valor 70.

Para encontrar o jogado com o valor de HITS igual ao valor máximo, podemos fazer:

SELECT NAME
FROM TEAMSTATS
WHERE HITS = MAX(HITS)

Porém, esta consulta nos retornará o erro:

Msg 147, Level 15, State 1, Line 3
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Este erro ocorreu pois tentamos incluir uma função de agragação (MAX, MIN, AVG ou COUNT) dentro de nossa cláusula WHERE. Apenas lendo o erro, podemos pensar em fazer:

SELECT NAME
FROM TEAMSTATS
HAVING HITS = MAX(HITS)

o que também nos retornará outro erro:

Msg 8121, Level 16, State 1, Line 3
Column 'TEAMSTATS.HITS' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Neste caso, a solução para o nosso problema é realizar uma sub consulta dentro da cláusula WHERE. Assim, existem duas soluções possíveis para este caso:

--1
SELECT NAME
FROM TEAMSTATS
WHERE HITS = (SELECT MAX(HITS) FROM TEAMSTATS)

Veja que inserimos nossa primeira consulta - SELECT MAX(HITS) FROM TEAMSTATS - no WHERE, retornando o resultado David.

--2
SELECT NAME
FROM TEAMSTATS
CROSS JOIN (
    SELECT MAX_VALUE = MAX(HITS)
    FROM TEAMSTATS
) T
WHERE HITS = MAX_VALUE

Agora, realizamos um CROSS JOIN para conseguir retornar o valor para MAX_VALUE e inseri-lo na cláusula WHERE. O CROSS JOIN retorna o produto cartesiano das linhas envolvidas no join. Ou seja, combinará todas as linhas da primeira tabela com todas as linhas da segunda tabela.

Voltando à sub consulta, temos algumas regras básicas que precisam ser seguidas:

• Devem estar entre parêntesis;
• As colunas da sub consulta devem as mesmas da consulta principal, para que a comparação seja efetuada;
• O ORDER BY não pode ser utilizado. O GROUP BY pode ser utilizado para obter o mesmo resultado que o ORDER BY na sub consulta;
• Uma sub consulta que retorne mais de uma linha somente pode ser utilizada com operadores de valores múltiplos, como o IN;
• O operador BETWEEN não pode ser utilizado.

Aqui, ainda não entramos na discussão de qual destas consultas seria mais eficiente que a outra. Isso fica para um futuro post.

Um abraço e até o próximo!