GROUP BY: colapsando linhas em grupos

Uma tabela, verificada diretamente, com seis pedidos em três status

ActiveRecord::Schema.define do
  create_table :scratch_orders, force: true do |t|
    t.string :status, null: false
    t.decimal :amount, precision: 10, scale: 2, null: false
    t.timestamps
  end
end

Seis linhas entraram nessa tabela: três "shipped", duas "pending", uma "cancelled". O curso SQL: WHERE & Operators já mostrou como puxar um status de cada vez, WHERE status = 'shipped' correspondendo a essas três linhas. GROUP BY faz uma pergunta diferente: não "me dá os shipped," mas "me dá uma linha por status, seja lá quais status existirem."

SELECT status, COUNT(*) AS cnt
FROM scratch_orders
GROUP BY status
ORDER BY status;

Rodado diretamente contra essa tabela real:

status    | cnt
----------+-----
cancelled | 1
pending   | 2
shipped   | 3

Seis linhas entraram, três linhas saíram. GROUP BY status dobrou toda linha compartilhando o mesmo valor de status numa única linha de saída, uma para cada valor distinto que a coluna guarda.

Por que toda outra coluna precisa ser um agregado

Assim que seis linhas colapsam em três, uma referência simples de coluna como amount deixa de fazer sentido sozinha. Dois valores reais alimentaram a linha "pending", então qual deles SELECT status, amount imprimiria? O PostgreSQL não chuta, ele recusa:

ERROR: column "scratch_orders.amount" must appear in the GROUP BY
clause or be used in an aggregate function

É por isso que COUNT(*) ficou ao lado de status acima em vez de amount. COUNT(*) é um agregado: uma função que transforma várias linhas num único número. Toda coluna nomeada depois de SELECT numa query agrupada está ou na lista do GROUP BY, ou envolta numa função agregada. Não existe uma terceira opção.

GROUP BY roda depois de WHERE, nunca antes

O curso SQL puro: SELECT e FROM já traçou o pipeline de execução real que uma query segue: FROM primeiro, WHERE depois, GROUP BY depois disso. Uma cláusula WHERE adicionada a essa query filtra linhas individuais antes delas serem dobradas em grupos:

SELECT status, COUNT(*) AS cnt
FROM scratch_orders
WHERE amount > 10
GROUP BY status
ORDER BY status;

status    | cnt
----------+-----
cancelled | 1
pending   | 2
shipped   | 2

O grupo "cancelled" continua lá: sua única linha tem amount = 30.00, bem acima de 10. A mudança está em "shipped". Uma das linhas tinha amount = 9.99, e o WHERE a filtrou antes do agrupamento, deixando duas linhas "shipped" para agrupar. O WHERE descarta linhas primeiro; o GROUP BY só vê o que sobra.

Pontos-chave

• GROUP BY coluna dobra toda linha compartilhando o valor daquela coluna numa única linha de saída, verificado diretamente: seis linhas entram, três status distintos saem.
• Toda coluna selecionada numa query agrupada precisa estar no GROUP BY ou envolta num agregado como COUNT(*); o PostgreSQL levanta um erro caso contrário.
• WHERE roda antes de GROUP BY no pipeline de execução que "SQL puro: SELECT e FROM" cobriu. Ele filtra linhas individuais antes de elas virarem um grupo.