COUNT, SUM, AVG: linhas viram números

Capítulo 3 — "O Pulso da Loja"

A credencial de "Analista" se acomoda no seu crachá como uma faixa âmbar e morna.

O bloco criptografado de K. nunca chegou a abrir, mas você lembra o título de cor:

Linhas brutas são ruído.
O sentido aparece quando você sabe somá-las.

QUERY apaga os holopainéis extras e deixa apenas um — vazio, com um único ponto pulsando no centro.

QUERY: Você vinha lendo o arquivo linha por linha. Chega. Hoje vamos ouvir o coração dele.

Até agora, você basicamente buscava linhas.

Por exemplo:

SELECT id, user_id, status, total_amount
FROM orders
WHERE status = 'paid';

Uma consulta assim mostra os pedidos pagos um por um:

Isso é útil quando você precisa examinar as linhas em si.

Mas uma pergunta de negócio costuma soar diferente:

Quantos pedidos foram pagos?
Qual é a receita total?
Qual é o ticket médio?
Qual foi o menor e o maior pedido?
Quantos compradores diferentes fizeram pedidos?

Perguntas assim não pedem uma lista longa de linhas. Pedem um resumo.

Para isso, o SQL tem as funções de agregação.

Uma função de agregação pega um conjunto de linhas e o condensa em um único valor final.

Por exemplo:

SELECT COUNT(*) AS orders_count
FROM orders;

Essa consulta não mostra cada pedido separadamente. Ela retorna um único número: quantas linhas existem na tabela orders.

Funções de agregação: muitas linhas → um valor

Os agregados transformam um fluxo de linhas em um indicador que dá para dizer em voz alta.

Não:

pedido 101
pedido 102
pedido 103
pedido 104

Mas sim:

4 pedidos ao todo

Não:

1200
3500
800
2100

Mas sim:

soma total 7600
ticket médio 1900
ticket mínimo 800
ticket máximo 3500

Cinco funções de agregação principais:

Por exemplo:

SELECT
    COUNT(*) AS orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders;

Se a consulta não tem GROUP BY, os agregados calculam o total sobre todo o conjunto de linhas que chegou até eles.

Ou seja, o resultado será uma única linha:

COUNT: quantas linhas

COUNT responde à pergunta "quantos?".

A forma mais comum:

COUNT(*)

Ela conta linhas.

Por exemplo:

SELECT COUNT(*) AS orders_count
FROM orders;

Resultado:

O asterisco em COUNT(*) não significa "selecione todas as colunas". Aqui, ele significa:

conte as próprias linhas.

COUNT(*) não olha para os valores dentro das colunas. Para ele, só importa se a linha existe ou não.

Por isso, COUNT(*) serve quando a pergunta é assim:

Quantos pedidos existem no total?
Quantos produtos existem no total?
Quantos usuários existem no total?
Quantas linhas restaram depois do filtro?

Por exemplo, quantos pedidos foram pagos:

SELECT COUNT(*) AS paid_orders_count
FROM orders
WHERE status = 'paid';

Aqui, primeiro o WHERE mantém apenas os pedidos pagos, e depois COUNT(*) conta as linhas restantes.

SUM, AVG, MIN e MAX

SUM calcula a soma.

SELECT SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid';

Assim você obtém a receita dos pedidos pagos.

AVG calcula o valor médio.

SELECT AVG(total_amount) AS avg_order_amount
FROM orders
WHERE status = 'paid';

Assim você obtém o ticket médio dos pedidos pagos.

MIN busca o valor mínimo.

SELECT MIN(total_amount) AS min_order_amount
FROM orders
WHERE status = 'paid';

MAX busca o valor máximo.

SELECT MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'paid';

Todas essas funções podem ser usadas juntas:

SELECT
    COUNT(*) AS paid_orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'paid';

Assim, uma única consulta retorna um resumo completo dos pedidos pagos.

O WHERE seleciona as linhas primeiro, o agregado calcula depois

É importante entender a ordem lógica aqui.

Nesta consulta:

SELECT
    COUNT(*) AS paid_orders_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid';

o filtro age primeiro:

WHERE status = 'paid'

Ele mantém apenas os pedidos pagos.

E só depois os agregados calculam o total sobre as linhas restantes:

COUNT(*)
SUM(total_amount)

Ou seja, a consulta não soma todos os pedidos para depois marcar os pagos de algum jeito. Ela primeiro remove as linhas que não interessam, e só então calcula o total.

Isso é útil para qualquer refinamento.

Receita apenas dos pedidos pagos:

WHERE status = 'paid'

Quantidade de pedidos cancelados:

WHERE status = 'cancelled'

Ticket médio em um período específico:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

Primeiro se forma o conjunto de linhas. Depois o agregado o transforma em um número.

QUERY: Antes de ouvir o pulso, escolha de quem é o coração que você está ouvindo. Todos os pedidos, os pedidos pagos e os pedidos cancelados soam diferente.

COUNT(*), COUNT(col), COUNT(DISTINCT col)

COUNT tem várias formas, e cada uma responde a uma pergunta diferente.

Vamos ver em um exemplo pequeno.

Suponha que exista uma tabela visits:

A consulta:

SELECT
    COUNT(*) AS rows_count,
    COUNT(promo_code) AS with_promo_code,
    COUNT(DISTINCT promo_code) AS different_promo_codes
FROM visits;

retorna:

Por quê?

COUNT(*) conta todas as linhas.

A tabela tem 5 linhas, então o resultado é 5.

COUNT(promo_code) conta as linhas em que promo_code está preenchido.

Ele não conta os valores NULL. Há códigos promocionais preenchidos em três linhas:

SALE
SALE
VIP

Então o resultado é 3.

COUNT(DISTINCT promo_code) conta os valores preenchidos distintos.

Dentre os valores preenchidos:

SALE
SALE
VIP

apenas dois são diferentes:

SALE
VIP

Então o resultado é 2.

Resumindo:

COUNT(*)                    → quantas linhas
COUNT(promo_code)           → em quantas linhas o código promocional está preenchido
COUNT(DISTINCT promo_code)  → quantos códigos promocionais diferentes e preenchidos existem

São três perguntas diferentes sobre os dados, por isso as respostas podem ser diferentes.

Agregados e NULL

A regra geral é:

os agregados costumam ignorar NULL.

Por exemplo, suponha os valores:

SUM(total_amount) soma apenas os valores preenchidos:

1000 + 2000 = 3000

AVG(total_amount) também usa apenas os valores preenchidos:

(1000 + 2000) / 2 = 1500

E não assim:

(1000 + 2000 + NULL) / 3

MIN e MAX também buscam o mínimo e o máximo apenas entre os valores preenchidos.

A única exceção importante é COUNT(*).

COUNT(*) conta linhas, não o valor de uma coluna específica, por isso o NULL dentro das colunas não interfere nele.

Compare:

SELECT
    COUNT(*) AS rows_count,
    COUNT(total_amount) AS filled_amounts,
    SUM(total_amount) AS total_sum,
    AVG(total_amount) AS avg_amount
FROM orders;

COUNT(*) responde à pergunta:

quantas linhas

COUNT(total_amount) responde à pergunta:

em quantas linhas total_amount está preenchido

Essas não são a mesma coisa.

Quando não sobra nenhuma linha depois do WHERE

Às vezes o filtro não encontra nenhuma linha.

Por exemplo:

SELECT
    COUNT(*) AS orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'status_that_does_not_exist';

Se não existem esses pedidos, o resultado ainda será uma única linha, porque um agregado sem GROUP BY retorna um resumo para o conjunto selecionado.

Mas os valores serão diferentes:

COUNT(*) retorna 0, porque não há linhas.

Já SUM, AVG, MIN e MAX retornam NULL, porque não há nada para somar, calcular a média ou comparar.

Se o relatório precisar de receita zero em vez de NULL, você pode usar COALESCE:

SELECT
    COUNT(*) AS orders_count,
    COALESCE(SUM(total_amount), 0) AS revenue
FROM orders
WHERE status = 'status_that_does_not_exist';

Isso não muda os dados na tabela. Apenas faz o resultado da consulta mostrar 0 em vez de um total vazio.

COUNT(*) e COUNT(1)

Às vezes você encontra isto em consultas escritas por outras pessoas:

COUNT(1)

Por exemplo:

SELECT COUNT(1)
FROM orders;

No PostgreSQL, para uma contagem comum de linhas, isso equivale a:

SELECT COUNT(*)
FROM orders;

Por quê?

Porque 1 é uma expressão que nunca é NULL. Ela existe em cada linha, então COUNT(1) conta cada linha.

Mas, para melhor legibilidade, é preferível escrever:

COUNT(*)

Assim fica claro de cara que você está contando linhas.

O mito de que COUNT(1) é necessariamente mais rápido que COUNT(*) não é uma regra útil no PostgreSQL. Por enquanto, lembre-se do jeito mais simples:

precisa contar linhas — escreva COUNT(*).

Por que uma coluna comum ao lado de um agregado dá erro

Olhe para esta consulta:

SELECT status, COUNT(*)
FROM orders;

Parece que ela deveria mostrar o status e a quantidade de pedidos.

Mas, sem GROUP BY, essa consulta falha com um erro.

Por quê?

COUNT(*) sem GROUP BY condensa todas as linhas selecionadas em uma única linha final.

Por exemplo:

Já status é uma coluna comum. Na tabela, pedidos diferentes têm status diferentes:

Se todo o conjunto de linhas colapsou em uma única linha de resultado, qual status o SQL deveria mostrar ao lado da contagem geral?

paid?
pending?
cancelled?

O SQL não adivinha. Ele exige que você descreva a lógica explicitamente.

Se você precisa da contagem geral de todos os pedidos, remova status:

SELECT COUNT(*) AS orders_count
FROM orders;

Se você precisa da quantidade de pedidos por status, adicione GROUP BY:

SELECT status, COUNT(*) AS orders_count
FROM orders
GROUP BY status;

Aí o resultado não será uma única linha geral, mas uma linha separada para cada status:

Vamos detalhar o GROUP BY mais adiante. Por enquanto, o importante é entender o motivo do erro:

o agregado condensa as linhas, mas a coluna comum continua tendo vários valores possíveis. O SQL não escolhe um valor por você.

QUERY: Linhas brutas mostram eventos. Agregados mostram o pulso: quantos, em que valor total, dentro de quais limites e em qual ritmo médio.