Artigo original: SQL Group By Tutorial: Count, Sum, Average, and Having Clauses Explained

A instrução GROUP BY é poderosa, mas, às vezes, pode causar confusão.

Mesmo após oito anos, sempre que utilizo o GROUP BY, eu tenho de parar e pensar o que exatamente está ocorrendo.

Neste artigo, nós vamos ver como criar uma instrução GROUP BY, o que ela faz em sua consulta e como você pode utilizá-la para gerar agregações e coletar insights sobre os seus dados.

O que vamos fazer:

  • Configurar o banco de dados.
  • Configurar os dados (criar vendas).
  • Como funciona um GROUP BY?.
  • Escrever instruções GROUP BY .
  • Agregações (COUNT, SUM, AVG).
  • Trabalhar com diversos grupos.
  • Utilizar funções dentro do GROUP BY.
  • Filtrar grupos com HAVING.
  • Agregar com agrupamentos implícitos.

Configurar o banco de dados

Antes de escrever nossas consultas, precisamos configurar nosso banco de dados.

Para os exemplos, vamos utilizar o PostgreSQL, mas as consultas e conceitos mostrados serão de fácil compreensão para qualquer sistema de banco de dados moderno (como o MySQL, o SQL Server e assim por diante).

Para trabalhar com nosso banco de dados PostgreSQL, podemos utilizar psql — o comando de linha interativo do PostgreSQL. Se você possui outro serviço de banco de dados de sua preferência, não há problema de trabalhar nele também.

Para começar, vamos criar nossa tabela de dados. Com o PostgreSQL instalado, podemos usar o comando  createdb <database-name> no console para criar um novo banco de dados. Chamei o meu de fcc:

$ createdb fcc

Agora, vamos iniciar o nosso console interativo utilizando o comando psql, e nos conectar ao banco de dados que acabamos de fazer \c <nome-do-banco>:

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#
Observação: eu limpei a saída do psql nestes exemplos para facilitar a leitura, então não se preocupe se as saídas mostradas aqui não sejam exatamente iguais ao que você verá no seu terminal.

Eu encorajo você a acompanhar estes exemplos e a fazer as consultas por conta própria. Você vai aprender e lembrar bem mais trabalhando nos exemplos ao invés de apenas lê-los.

Configurar os dados (criar vendas).

Para os nossos exemplos, vamos utilizar um banco que guarde o histórico de vendas de vários produtos através de diferentes localizações.

Iremos chamar esta tabela de vendas. Ela será uma representação simples de uma loja: nome do local, nome do produto, preço e a data em que o produto foi vendido.

Se fossemos construir este banco em uma aplicação real, configuraríamos as chaves estrangeiras para outras tabelas (como local ou produtos). No entanto, para ilustrar os conceitos de GROUP BY, vamos utilizar apenas colunas do tipo TEXT.

Vamos criar a tabela e inserir alguns dados de vendas:

CREATE TABLE vendas(
  local TEXT,
  produto TEXT,
  preco DECIMAL,
  vendido_em TIMESTAMP
);

INSERT INTO vendas(local, produto, preco, vendido_em) VALUES
('Central', 'Café', 2, NOW()),
('Central', 'Café', 2, NOW() - INTERVAL '1 hora'),
('Centro', 'Biscoito', 3, NOW() - INTERVAL '2 hora'),
('Centro', 'Café', 2, NOW() - INTERVAL '1 dia'),
('Central', 'Biscoito', 2, NOW() - INTERVAL '2 dias'),
('Rua 1', 'Biscoito', 3, NOW() - INTERVAL '2 dias' - INTERVAL '1 hora'),
('Rua 1', 'Café', 2, NOW() - INTERVAL '3 dias'),
('Central', 'Biscoito', 3, NOW() - INTERVAL '3 dias' - INTERVAL '1 hora');

Temos 3 localizações: Central, Centro e Rua 1.

Nós temos dois produtos, Café e Biscoito, e inserimos diferentes valores em vendido_em para representar os itens sendo vendidos em dias e horas diferentes.

Existem algumas vendas na data de hoje, algumas na data de ontem, e algumas em dias anteriores a ontem.

Como funciona um GROUP BY?

Para ilustrar como as instruções GROUP BY funcionam, vamos primeiro falar através de um exemplo.

Imagine que temos um quarto cheio de pessoas que nasceram em países diferentes.

Se quiséssemos descobrir a média da altura das pessoas no quarto por país, primeiro, pediríamos para as pessoas se separarem em grupos baseados no país em que nasceram.

Uma vez separados em seus devidos grupos, poderíamos calcular a média de altura de qualquer um desses grupos.

É assim que funcionam as instruções GROUP BY. Primeiro, definimos como queremos agrupar as informações – então, podemos realizar cálculos ou agregações nos grupos.

Diversos grupos

Podemos agrupar os dados em quantos grupos ou subgrupos quisermos.

Por exemplo, depois de pedirmos para as pessoas se separarem baseados no seu país de nascimento, podemos dizer para cada um desses grupos que se separem em grupos baseados na cor de seus olhos.

Ao fazer isso, temos grupos de pessoas baseados na combinação do seu país de nascimento e na cor de seus olhos.

Agora, podemos encontrar a média da altura dentro de cada um desses pequenos grupos. Teremos um resultado mais específico: média de altura por país e cor dos olhos.

As instruções GROUP BY são frequentemente usadas para situações onde você utiliza a frase por alguma coisa ou para cada alguma coisa:

  • Média de altura por país
  • Número total de pessoas para cada combinação de cor de olhos e cor de cabelo
  • Total de vendas por produto

Escrever instruções GROUP BY

Uma instrução GROUP BY é muito fácil de escrever — apenas utilizamos a palavra chave GROUP BY e então especificamos os campos que queremos agrupar:

SELECT ...
FROM vendas
GROUP BY local;

Esta consulta simples agrupa nossos dados de  vendas pela coluna do local.

Fizemos o agrupamento — mas e agora? O que colocamos em nosso SELECT?

A coisa mais clara para selecionar é o nosso local— nós agrupamos para que ao menos vejamos o nome dos grupos que fizemos:

SELECT location
FROM sales
GROUP BY location;

O resultado é as nossas três localizações:

  local
------------
 Rua 1
 Central
 Centro
(3 rows)

Se olharmos para a tabela bruta de dados  (SELECT * FROM vendas;), veremos que temos 4 linhas com o local Central, duas linhas com o local Centro, e duas linhas com o local Rua 1:

 produto 	|    local 	| preco |          vendido_em
---------+---------+------------+-------+----------------------------
 Cafe    	| Central    	|     2 | 2020-09-01 09:42:33.085995
 Cafe  	 	| Central    	|     2 | 2020-09-01 08:42:33.085995
 Biscoito	| Centro     	|     3 | 2020-09-01 07:42:33.085995
 Cafe  	 	| Centro     	|     2 | 2020-08-31 09:42:33.085995
 Biscoito	| Central    	|     2 | 2020-08-30 09:42:33.085995
 Biscoito	| Rua 1   	|     3 | 2020-08-30 08:42:33.085995
 Cafe  	 	| Rua 1		|     2 | 2020-08-29 09:42:33.085995
 Biscoito	| Central    	|     3 | 2020-08-29 08:42:33.085995
(8 rows)

Agrupando na coluna local, nosso banco de dados pega essas linhas de input e identifica locais únicos dentre eles — estes locais únicos servem como nosso "grupo".

E quanto às outras colunas da nossa tabela?

Se tentarmos selecionar a coluna produto que não agrupamos...

SELECT
  local,
  produto
FROM vendas
GROUP BY local;

...receberemos o seguinte erro:

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

O problema aqui é que pegamos oito linhas e as esprememos ou as transformamos em três.

Não podemos apenas retornar o resto das colunas como normalmente fazíamos — nós possuíamos oito linhas, e agora temos três.

O que faremos com as cincos linhas restantes de dados? Quais das oito linhas de dados devem ser mostradas nestas três linhas de locais distintos?

Não há uma resposta clara e definitiva aqui.

Para utilizar o resto da nossa tabela de dados, nós também temos de analisar os dados do restante das colunas dentro dos nossos três grupos de local.

Isto significa que temos de agregar ou realizar um cálculo para produzir um tipo de resumo das informações sobre os dados restantes.

Agregações (COUNT, SUM, AVG)

Uma vez que definimos como agrupar nossos dados, podemos realizar agregações com as colunas restantes.

São coisas como contar o número de linhas por grupo, somar um valor em específico através do grupo, ou calcular a média das informações dentro do grupo.

Para começar, vamos encontrar o número de vendas por local.

Como cada gravação em nossa tabela de vendas é uma venda, o número de vendas por local seria o número de linhas dentro de cada local do grupo.

Para fazer isto, utilizaremos a função de agregação COUNT() para contar o número de linhas de cada grupo:

SELECT
  local,
  COUNT(*) AS numero_de_vendas
FROM vendas
GROUP BY local;

Nós usamos COUNT(*), que conta todas as linhas de input por grupo (COUNT() também funciona com expressões, mas possui um comportamento levemente diferente).

Abaixo, vemos como o banco de dados executa esta consulta:

  • FROM vendas — Primeiro, retornamos todos os dados da tabela vendas
  • GROUP BY local — Em seguida, determinamos os grupos únicos de local
  • SELECT ... — Finalmente, selecionamos o nome do local e contamos o número de linhas daquele grupo

Nomearemos a contagem de linhas usando AS numero_de_vendas para fazer uma saída de dados mais legível. Se parecerá com o seguinte:

   local    | numero_de_vendas
------------+-----------------
 Rua 1      |       2
 Central    |       4
 Centro     |       2
(3 rows)

O local Rua 1 possui duas vendas, Central possui quatro, e Centro possui duas.

Aqui, podemos ver como pegamos as demais colunas de dados das nossas oito linhas independentes e as colocamos em um resumo das informações para cada local: o número de vendas.

De um jeito similar, ao invés de contar o número de linha em um grupo, podemos somar as informações dentro do grupo — como o dinheiro total ganho nesses locais.

Para fazer isto, utilizamos a função SUM() :

SELECT
  local,
  SUM(preco) AS ganho_total
FROM vendas
GROUP BY local;

No lugar de contar o número de linhas em cada grupo, somamos o total de ganhos de cada venda. Isto mostra o ganho total por local:

  local	    | ganho_total
------------+---------------
 Rua 1      |             5
 Central    |             9
 Centro     |             5
(3 rows)

Média (AVG)

Encontrar a média do valor de vendas por local significa apenas que trocaremos a função SUM() para a função AVG():

SELECT
  local,
  AVG(preco) AS media_de_ganho
FROM vendas
GROUP BY local;

Trabalhar com diversos grupos

Até o momento, temos trabalho apenas com um grupo: local.

E se quisermos subdividir o grupo ainda mais?

É semelhante ao cenário "País de nascimento e cor dos olhos" com o qual começamos. E se quiséssemos encontrar o número de vendas por produto e por local?

Para fazermos tudo isso, precisamos adicionar uma segunda condição de agregação ao declararmos o GROUP BY :

SELECT ...
FROM vendas
GROUP BY local, produto;

Ao adicionarmos uma segunda coluna GROUP BY, nós subdividimos ainda mais o nosso grupo de locais em grupo de locais por produto.

Agora que estamos agrupando também pela coluna produto, podemos retornar ao nosso SELECT (adicionarei a instrução ORDER BY nestas consultas para facilitar a leitura dos dados de saída.)

SELECT
  local,
  produto
FROM vendas
GROUP BY local, produto
ORDER BY local, produto;

Olhando para o resultado de nosso novo agrupamento, podemos ver nossas combinações únicas de local/produto:

  local     | produto
------------+---------
 Rua 1      | Biscoito
 Rua 1      | Café
 Centro     | Biscoito
 Centro     | Café
 Central    | Biscoito
 Central    | Café
(6 rows)

Agora que temos nossos grupos, o que queremos fazer com o resto das nossas colunas de dados?

Bem, podemos encontrar o número de vendas por produto e por local utilizando a mesma função de agregação utilizada antes:

SELECT
  local,
  produto,
  COUNT(*) AS numero_de_vendas
FROM vendas
GROUP BY local, produto
ORDER BY local, produto;
  local  | produto    | numero_de_vendas
------------+---------+-----------------
 Rua 1   | Biscoito   |               1
 Rua 1   | Café       |               1
 Centro  | Biscoito   |               1
 Centro  | Café       |               1
 Central | Biscoito   |               2
 Central | Café       |               2
(6 rows)
Como exercício para o leitor™: encontre o total de ganhos (soma) de cada produto por local.

Utilizar funções dentro do GROUP BY

Agora, tentaremos achar o número total de vendas por dia.

Se seguirmos padrões similares como fizemos com os nossos locais e agrupamos pela nossa coluna vendido_em...

SELECT
  vendido_em,
  COUNT(*) AS vendas_por_dia
FROM vendas
GROUP BY vendido_em
ORDER BY vendido_em;

...esperamos que o resultado seja ter cada grupo em um único dia— no entanto, temos isso:

          vendido_em        | vendas_por_dia
----------------------------+---------------
 2020-08-29 08:42:33.085995 |             1
 2020-08-29 09:42:33.085995 |             1
 2020-08-30 08:42:33.085995 |             1
 2020-08-30 09:42:33.085995 |             1
 2020-08-31 09:42:33.085995 |             1
 2020-09-01 07:42:33.085995 |             1
 2020-09-01 08:42:33.085995 |             1
 2020-09-01 09:42:33.085995 |             1
(8 rows)

Parece que nossos dados não estão agrupados — temos cada linha individualmente.

Nossos dados, no entanto, estão agrupados! O problema é que cada linha de vendido_em possui um valor único — então, nossas linhas são seus próprios grupos!

O GROUP BY está funcionando corretamente, mas não é a saída que queremos.

O que ocasiona esta situação é a informação extra do carimbo de data e hora.

Cada um destes carimbos se diferenciam por horas, minutos ou segundos — assim, eles são colocados em seu devido lugar em seu próprio grupo.

Precisamos converter cada um dos valores de data e hora para apenas data:

  • 2020-09-01 08:42:33.085995 => 2020-09-01
  • 2020-09-01 09:42:33.085995 => 2020-09-01

Convertido para uma data, todos os carimbos do mesmo dia retornarão apenas a referência do valor da data em que foi efetuada a compra—e será colocado dentro do mesmo grupo.

Para fazermos isso, converteremos os dados de vendido_em para data:

SELECT
  vendido_em::DATE AS date,
  COUNT(*) AS vendas_por_dia
FROM vendas
GROUP BY vendido_em::DATE
ORDER BY vendido_em::DATE;

Em nossa instrução GROUP BY, utilizamos ::DATE para cortar uma parte do carimbo de data/hora para apenas dia. Isto corta efetivamente as horas/minutos/segundos do carimbo e retorna apenas o dia.

No nosso SELECT, retornamos a mesma expressão e damos a ela um nome para deixar o resultado mais bonito.

Pela mesma razão, não podemos retornar o produto sem agrupar ou realizar algum tipo de agrupamento. O banco de dados não deixará retornar apenas o vendido_em — tudo que está no SELECT deve estar dentro do GROUP BY ou em algum tipo de agregação nos grupos resultantes.

O resultado de vendas por dia que originalmente queríamos ver:

    date    | vendas_por_dia
------------+---------------
 2020-08-29 |             2
 2020-08-30 |             2
 2020-08-31 |             1
 2020-09-01 |             3
(4 rows)

Filtrar grupos com HAVING

Dando continuidade, vamos verificar como filtrar nossas linhas agrupadas.

Para fazer isso, vamos tentar encontrar dias onde houve mais de uma venda.

Sem agrupar, normalmente filtraríamos nossas linhas utilizando a instrução. Por exemplo:

SELECT *
FROM vendas
WHERE produto = 'Café';

Com nossos grupos, utilizaremos algo parecido com isso para filtrá-los com base na quantidade de linhas...

SELECT
  vendido_em::DATE AS date,
  COUNT(*) AS vendas_por_dia
FROM vendas
WHERE COUNT(*) > 1      -- filtrar os grupos?
GROUP BY vendido_em::DATE;

Infelizmente, isso não funciona e recebemos este erro:

ERROR:  aggregate functions are not allowed in WHERE

Funções agregadas não são permitidas dentro da instrução WHERE, pois ela é avaliada antes da instrução GROUP BY—não há nenhum grupo para realizar os cálculos.

Existe, contudo, um tipo de instrução que nos permite filtrar e realizar agregações. Ela é avaliada depois da instrução GROUP BY: a instrução HAVING.

A instrução HAVING é como a instrução WHERE para os seus grupos.

Para encontrar os dias em que houve mais de uma venda, adicionamos a instrução HAVING que verificará a quantidade de linhas dentro do grupo:

SELECT
  vendido_em::DATE AS date,
  COUNT(*) AS vendas_por_dia
FROM sales
GROUP BY vendido_em::DATE
HAVING COUNT(*) > 1;

A instrução HAVING filtra qualquer linha onde a contagem de linhas deste grupo não é maior que um. Vemos isso no nosso resultado:

    date    | vendas_por_dia
------------+---------------
 2020-09-01 |             3
 2020-08-29 |             2
 2020-08-30 |             2
(3 rows)

Apenas para deixar completo, segue em ordem a execução de todas as partes de uma instrução SQL:

  • FROM — Retorna todas as linhas da tabela FROM
  • JOIN — Realiza quaisquer junções
  • WHERE — Filtra linhas
  • GROUP BY — Forma grupos
  • HAVING — Filtra grupos
  • SELECT — Seleciona os dados a serem retornados
  • ORDER BY — Ordena a saída de linhas
  • LIMIT — Retorna uma certa quantidade de linhas

Agregações com agrupamentos implícitos

O último tópico que veremos é a agregação que pode ser realizada sem o GROUP BY— ou, melhor dizendo, que possui um agrupamento implícito.

Estas agregações são úteis em cenários em que você quer encontrar um agrupamento específico dentro de uma tabela — como o total de ganhos ou o maior ou menor valor da coluna.

Por exemplo, podemos encontrar o total de ganhos de todos os locais selecionando a soma da tabela inteira:

SELECT SUM(preco)
FROM vendas;
 sum
-----
  19
(1 row)

Até o momento fizemos $19 de vendas em todos os locais (vivaaaaa!).

Outra coisa útil que podemos consultar é o primeiro ou o último valor de algo.

Por exemplo, qual a data da nossa primeira venda?

Para encontrá-la, basta utilizarmos a função  MIN():

SELECT MIN(vendido_em)::DATE AS primeira_venda
FROM vendas;
 primeira_venda
------------
 2020-08-29
(1 row)

(Para encontrar a data da última venda, bastar substituir MIN() por MAX().)

Como usar MIN / MAX

Embora essas consultas simples possam ser utilizadas em consultas independentes, elas são frequentemente parte de filtros de consultas maiores.

Por exemplo, vamos tentar encontrar o total de vendas do último dia em que tivemos vendas.

Um jeito de podermos escrever esta consulta seria o seguinte:

SELECT
  SUM(preco)
FROM vendas
WHERE vendido_em::DATE = '2020-09-01';

Esta consulta funciona, mas obviamente codificando a data de 2020-09-01.

09/01/2020 talvez seja o último dia em que tivemos uma venda, mas nem sempre será essa data. Precisamos de uma solução dinâmica.

Conseguimos isto através da combinação desta consulta com a função MAX() em uma subconsulta:

SELECT
  SUM(preco)
FROM venda
WHERE vendido_em::DATE = (
  SELECT MAX(vendido_em::DATE)
  FROM vendas
);

Na nossa instrução WHERE, encontramos a data mais antiga dentro da tabela utilizando a subconsulta: SELECT MAX(vendido_em::DATE) FROM vendas.

Então, utilizamos esta "data máxima" como um valor para filtrar a tabela, e somamos cada uma das vendas.

Agrupamento implícito

Chamamos de agrupamento implícito, pois, se tentarmos selecionar e agregar  o valor com uma coluna não agregada dessa forma...

SELECT
  SUM(preco),
  local
FROM vendas;

...teremos um erro familiar:

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

GROUP BY é uma ferramenta

Como muitos dos tópicos em desenvolvimento de software, GROUP BY é uma ferramenta.

Existem várias maneiras de escrever e reescrever estas consultas utilizando a combinação de  GROUP BY, funções agregadas ou outras ferramentas como  DISTINCT, ORDER BY e LIMIT.

Entender e trabalhar com o GROUP BY requer um pouco de pratica, mas ao aprender, você descobrirá um conjunto inteiramente novo de problemas que você poderá resolver!

Se gostou deste post, você pode seguir o autor deste artigo no Twitter, onde ele fala sobre banco de dados e sobre como ter sucesso na carreira como desenvolvedor.

Obrigado pela leitura!