SUM e AVG com NULLs

Aqui está uma pergunta clássica de entrevistas que prejudica candidatos desatentos: "Você tem uma coluna de salários com alguns NULLs. O que AVG(salary) calcula e isso corresponde ao que a empresa precisa?"

A resposta revela se você entende que as funções de agregação ignoram NULLs, o que altera o denominador de uma média. Se você errar isso em produção, a média informada ficará silenciosamente superestimada.

Vamos tornar esse comportamento inequívoco.

Use esta tabela employees, com uma coluna bonus que aceita NULL, durante toda a lição:

• Alice, bônus 100
• Bob, bônus 200
• Carol, bônus NULL
• Dan, bônus 300

São quatro linhas, três bônus não NULL e um NULL. Executaremos SUM e AVG sobre esses dados e observaremos como NULL é tratado.

SUM(bonus) soma apenas os valores não NULL: 100 + 200 + 300 = 600. A linha com NULL não contribui com nada; ela simplesmente é ignorada, não tratada como zero no sentido aritmético de alterar a contagem.

Na prática, o efeito é o mesmo que considerar NULL ausente. SUM nunca gera erros por causa de NULLs e nunca retorna NULL, a menos que todas as entradas sejam NULL.

AVG(bonus) é o ponto crucial. Ele calcula a soma dos valores não NULL dividida pela contagem dos valores não NULL: 600 / 3 = 200.

O denominador é 3, não 4. A linha com NULL é excluída tanto do numerador quanto do divisor. É exatamente por isso que AVG pode surpreender: a média considera os valores presentes, não todas as linhas.

Suponha que o significado empresarial de um bônus NULL seja "não recebeu bônus" = 0. Nesse caso, a média correta deveria ser 600 / 4 = 150, mas AVG(bonus) informa 200.

A resposta adequada em uma entrevista é: "AVG ignora NULLs, então calcula a média apenas dos funcionários que têm um bônus. Se NULL significar zero, preciso converter os NULLs para 0 primeiro." Explicitar essa diferença é o que garante o ponto.

Para calcular a média considerando todas as linhas e tratando NULL como 0, envolva a coluna em COALESCE(bonus, 0). Agora todas as linhas têm um valor numérico, então o denominador passa a ser 4.

Isso produz 600 / 4 = 150. A lição é: AVG(col) e AVG(COALESCE(col, 0)) respondem a perguntas empresariais diferentes. Escolha conscientemente.

Uma igualdade útil: AVG(col) é igual a SUM(col) / COUNT(col) — observe que é COUNT(col), não COUNT(*), pois AVG e esse COUNT ignoram NULLs.

Se você escrever SUM(col) / COUNT(*) por engano, obterá a média considerando todas as linhas (150 neste caso), que é diferente de AVG (200). Às vezes, os entrevistadores pedem que você reconstrua AVG manualmente para verificar se escolhe o COUNT correto.

Um erro sutil ao calcular médias manualmente: em muitos bancos de dados, dividir dois inteiros realiza uma divisão inteira, truncando as casas decimais. 7 / 2 pode resultar em 3, não em 3,5.

O próprio AVG geralmente retorna um número decimal, mas, se você o reconstruir com SUM / COUNT em colunas inteiras, poderá perder precisão. Multiplique por 1.0 ou converta primeiro para um tipo decimal.

Um caso extremo que os entrevistadores adoram: e se todos os valores forem NULL ou o filtro não encontrar nenhuma linha?

• SUM retorna NULL (não 0) quando não há entradas não NULL.
• AVG também retorna NULL, pois a divisão por uma contagem igual a zero é indefinida.
• COUNT, por outro lado, retorna 0.

Envolva o resultado em COALESCE(SUM(col), 0) se precisar de um valor numérico padrão.

As mesmas regras de NULL se aplicam dentro de GROUP BY. A média AVG de cada grupo é dividida pela contagem de valores não NULL daquele grupo. Um grupo composto somente por bônus NULL produz AVG = NULL para esse grupo.

Por isso, quando encontrar médias por departamento inesperadas, desconfie primeiro de NULLs reduzindo os denominadores individuais, antes de suspeitar de um erro de junção.

Uma resposta refinada em uma entrevista seria: "SUM e AVG ignoram NULLs. AVG divide pela contagem de valores não NULL, então os NULLs efetivamente reduzem o denominador. Se NULL tiver que contar como zero, eu o converto usando COALESCE antes de fazer a agregação; caso contrário, a média refletirá apenas as linhas que têm um valor."

Essa única frase demonstra correção, visão de negócio e a solução.

Aplique a regra aos dados de exemplo.

Principais conclusões sobre SUM e AVG com NULLs:

• Ambos ignoram NULLs completamente.
• AVG(col) = SUM(col) / COUNT(col) — o denominador exclui NULLs.
• Use COALESCE(col, 0) quando NULL significar zero e precisar ser contabilizado.
• Entradas compostas apenas por NULLs ou sem linhas fazem SUM e AVG retornarem NULL (COUNT retorna 0).
• Tenha cuidado com a divisão inteira ao reconstruir AVG manualmente.

A seguir: MIN, MAX e agregação de dados não numéricos.