Agregação Condicional em SQL com CASE

Lição 4.4 · Tempo de leitura: ~8 min · Tarefas para praticar ↓

A agregação condicional calcula várias métricas numa única consulta SQL colocando uma expressão CASE dentro de uma função de agregação: SUM(CASE WHEN ... THEN ... END). Nesta lição, você vai aprender somas, contagens e médias condicionais, os erros com ELSE que geram números errados e como transformar linhas em colunas (pivot).

Com GROUP BY você obtém um valor por grupo: o número de pagamentos, o valor total. Os relatórios geralmente precisam de mais: para cada funcionário, quantos pagamentos foram pequenos, médios e grandes, e quanto cada faixa rendeu. Sem agregação condicional, seria preciso executar três consultas com filtros WHERE diferentes e combinar os resultados à mão.

A Ideia Principal

Uma função de agregação processa todas as linhas do grupo. Um CASE dentro dela decide qual valor cada linha contribui:

SUM(CASE WHEN condition THEN value ELSE 0 END)
COUNT(CASE WHEN condition THEN 1 END)
AVG(CASE WHEN condition THEN value END)

As linhas que não satisfazem a condição contribuem com 0 para uma soma ou com NULL, que as funções de agregação ignoram. Assim, cada métrica é calculada apenas sobre as "suas" linhas, enquanto todas as métricas são calculadas numa única passagem pela tabela.

Soma Condicional

SELECT staff_id,
       SUM(CASE WHEN amount < 2 THEN amount ELSE 0 END) AS low_total,
       SUM(CASE WHEN amount >= 2 AND amount < 6 THEN amount ELSE 0 END) AS medium_total,
       SUM(CASE WHEN amount >= 6 THEN amount ELSE 0 END) AS high_total,
       SUM(amount) AS total
FROM payment
GROUP BY staff_id;

Resultado: para cada funcionário, a receita de pagamentos pequenos, médios e grandes, e o total. As três faixas somadas dão o total.

Para SUM, ELSE 0 é o correto: as linhas fora da faixa não acrescentam nada. Sem ELSE, uma faixa sem nenhuma linha correspondente devolve NULL em vez de 0.

Contagem Condicional

SELECT staff_id,
       COUNT(CASE WHEN amount < 2 THEN 1 END) AS low_payments,
       COUNT(CASE WHEN amount >= 2 AND amount < 6 THEN 1 END) AS medium_payments,
       COUNT(CASE WHEN amount >= 6 THEN 1 END) AS high_payments
FROM payment
GROUP BY staff_id;

Resultado: o número de pagamentos em cada faixa para cada funcionário.

COUNT(expression) conta valores que não são NULL. Quando a condição é falsa e não há ELSE, o CASE devolve NULL e a linha não é contada.

Um erro comum: COUNT(CASE WHEN ... THEN 1 ELSE 0 END). Zero não é NULL, então o COUNT conta todas as linhas e as três colunas mostram o mesmo número. Se preferir ELSE 0, use SUM: SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Uma coluna também pode ser contada diretamente. COUNT(return_date) conta apenas os aluguéis devolvidos:

SELECT staff_id,
       COUNT(*) AS all_rentals,
       COUNT(return_date) AS returned,
       COUNT(CASE WHEN return_date IS NULL THEN 1 END) AS not_returned
FROM rental
GROUP BY staff_id;

Resultado: para cada funcionário, todos os aluguéis, os devolvidos e os que ainda não foram devolvidos.

Média Condicional

SELECT staff_id,
       AVG(amount) AS avg_payment,
       AVG(CASE WHEN amount >= 5 THEN amount END) AS avg_big_payment
FROM payment
GROUP BY staff_id;

Resultado: para cada funcionário, a média de todos os pagamentos e a média dos pagamentos de $5 ou mais.

Para AVG, não escreva ELSE 0. A média é a soma dividida pelo número de valores; os zeros seriam contados como valores e reduziriam a média. Sem ELSE, as linhas que não correspondem dão NULL e são ignoradas.

Pivot: Transformar Linhas em Colunas

Um pivot mostra os valores de uma categoria como colunas separadas. Um GROUP BY rental_rate, rating normal devolve uma linha por combinação. Com agregação condicional, cada classificação vira uma coluna:

SELECT rental_rate,
       COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g,
       COUNT(CASE WHEN rating = 'PG' THEN 1 END) AS pg,
       COUNT(CASE WHEN rating = 'PG-13' THEN 1 END) AS pg13,
       COUNT(CASE WHEN rating = 'R' THEN 1 END) AS r,
       COUNT(CASE WHEN rating = 'NC-17' THEN 1 END) AS nc17,
       COUNT(*) AS all_films
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

Resultado: três linhas, uma por valor de aluguel ($0.99, $2.99, $4.99), com o número de filmes de cada classificação em colunas separadas.

O mesmo funciona com outras agregações. A duração média dos filmes por classificação, novamente sem ELSE:

SELECT rental_rate,
       ROUND(AVG(CASE WHEN rating = 'G' THEN length END), 1) AS g_avg_length,
       ROUND(AVG(CASE WHEN rating = 'R' THEN length END), 1) AS r_avg_length
FROM film
GROUP BY rental_rate
ORDER BY rental_rate;

Um pivot por dia da semana, construído a partir de uma data:

SELECT MONTH(rental_date) AS rental_month,
       SUM(CASE WHEN DAYOFWEEK(rental_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_rentals,
       SUM(CASE WHEN DAYOFWEEK(rental_date) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS weekday_rentals
FROM rental
GROUP BY MONTH(rental_date)
ORDER BY rental_month;

Resultado: para cada mês, o número de aluguéis nos fins de semana e nos dias úteis. No MySQL, DAYOFWEEK() devolve 1 para domingo e 7 para sábado.

Alguns bancos de dados têm um operador PIVOT (SQL Server, Oracle), mas a agregação condicional funciona em todos e é a opção portável.

Proporções e Percentuais

Duas agregações condicionais podem ser combinadas numa só expressão:

SELECT customer_id,
       COUNT(*) AS payments,
       ROUND(100 * SUM(CASE WHEN amount >= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS big_payment_pct
FROM payment
GROUP BY customer_id
ORDER BY big_payment_pct DESC
LIMIT 10;

Resultado: os 10 clientes com a maior proporção de pagamentos de $5 ou mais.

A Cláusula FILTER

PostgreSQL e SQLite (3.30+) suportam uma sintaxe padrão mais curta, FILTER:

-- PostgreSQL, SQLite
SELECT customer_id,
       COUNT(*) AS all_payments,
       COUNT(*) FILTER (WHERE amount >= 5) AS big_payments,
       SUM(amount) FILTER (WHERE amount >= 5) AS big_total
FROM payment
GROUP BY customer_id;

Significa o mesmo que COUNT(CASE WHEN amount >= 5 THEN 1 END). MySQL, MariaDB e SQL Server não suportam FILTER, por isso o CASE continua a ser a forma portável.

Principais conclusões:

• A agregação condicional é uma função de agregação com CASE dentro: várias métricas numa consulta e numa passagem.
• SUM(CASE ... ELSE 0 END) para somas; COUNT(CASE ... END) sem ELSE para contagens.
• COUNT(CASE ... ELSE 0 END) conta todas as linhas: um erro comum.
• AVG(CASE ...) sem ELSE, senão os zeros reduzem a média.
• Um pivot com CASE transforma valores de uma categoria em colunas e funciona em qualquer banco de dados.

Perguntas Frequentes

O que é agregação condicional em SQL?

É uma função de agregação cujo argumento é uma expressão CASE, por exemplo SUM(CASE WHEN amount >= 5 THEN amount ELSE 0 END). Cada métrica é calculada apenas sobre as linhas que satisfazem a sua condição, por isso uma consulta pode devolver muitas métricas com filtros diferentes.

Por que COUNT(CASE WHEN ... ELSE 0 END) conta todas as linhas?

O COUNT conta valores que não são NULL, e 0 não é NULL. Remova o ELSE para que as linhas que não correspondem devolvam NULL, ou use SUM(CASE WHEN ... THEN 1 ELSE 0 END).

Como transformar linhas em colunas no MySQL?

O MySQL não tem operador PIVOT. Use uma agregação condicional por coluna de saída, COUNT(CASE WHEN rating = 'G' THEN 1 END) AS g, e agrupe pela dimensão das linhas.