Tudo o que você precisa saber sobre a instrução SQL GROUP BY

Muito do poder dos bancos de dados relacionais vem da filtragem de dados e da junção de tabelas. É por isso que representamos essas relações em primeiro lugar. Mas os sistemas de banco de dados modernos fornecem outra técnica valiosa: agrupamento.

O agrupamento permite que você extraia informações resumidas de um banco de dados. Ele permite combinar resultados para criar dados estatísticos úteis. O agrupamento evita que você escreva código para casos comuns, como listas de cálculo de média. E pode tornar os sistemas mais eficientes.

O que a cláusula GROUP BY faz?

GROUP BY, como o nome sugere, agrupa os resultados em um conjunto menor. Os resultados consistem em uma linha para cada valor distinto da coluna agrupada. Podemos mostrar seu uso examinando alguns dados de amostra com linhas que compartilham alguns valores comuns.

O seguinte é um banco de dados muito simples com duas tabelas que representam álbuns de discos. Você pode configurar esse banco de dados escrevendo um esquema básico para o sistema de banco de dados escolhido. A tabela de álbuns tem nove linhas com uma coluna de ID de chave primária e colunas para nome, artista, ano de lançamento e vendas:

 +----+---------------------------+-----------+--------------+-------+
 | id | name | artist_id | release_year | sales |
 +----+---------------------------+-----------+--------------+-------+
 | 1 | Abbey Road | 1 | 1969 | 14 |
 | 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
 | 3 | Rumours | 3 | 1977 | 28 |
 | 4 | Nevermind | 4 | 1991 | 17 |
 | 5 | Animals | 2 | 1977 | 6 |
 | 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
 | 7 | 21 | 6 | 2011 | 25 |
 | 8 | 25 | 6 | 2015 | 22 |
 | 9 | Bat Out of Hell | 7 | 1977 | 28 |
 +----+---------------------------+-----------+--------------+-------+

A mesa dos artistas é ainda mais simples. Possui sete linhas com colunas id e name:

 +----+---------------+
 | id | name |
 +----+---------------+
 | 1 | The Beatles |
 | 2 | Pink Floyd |
 | 3 | Fleetwood Mac |
 | 4 | Nirvana |
 | 5 | Elton John |
 | 6 | Adele |
 | 7 | Meat Loaf |
 +----+---------------+

Você pode compreender vários aspectos do GROUP BY com apenas um conjunto de dados simples como este. Claro, um conjunto de dados da vida real teria muitas, muitas mais linhas, mas os princípios permanecem os mesmos.

Agrupamento por uma única coluna

Digamos que queremos descobrir quantos álbuns temos para cada artista. Comece com uma consulta SELECT típica para buscar a coluna artist_id:

 SELECT artist_id FROM albums

Isso retorna todas as nove linhas, conforme o esperado:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 2 |
 | 5 |
 | 6 |
 | 6 |
 | 7 |
 +-----------+

Para agrupar esses resultados pelo artista, anexe a frase GROUP BY artist_id :

 SELECT artist_id FROM albums GROUP BY artist_id

O que dá os seguintes resultados:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 +-----------+

Existem sete linhas no conjunto de resultados, reduzidas do total de nove na tabela de álbuns . Cada artist_id exclusivo possui uma única linha. Finalmente, para obter as contagens reais, adicione COUNT (*) às colunas selecionadas:

 SELECT artist_id, COUNT(*)
 FROM albums
 GROUP BY artist_id
 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 1 | 1 |
 | 2 | 2 |
 | 3 | 1 |
 | 4 | 1 |
 | 5 | 1 |
 | 6 | 2 |
 | 7 | 1 |
 +-----------+----------+

Os resultados agrupam dois pares de linhas para os artistas com ids 2 e 6 . Cada um possui dois álbuns em nosso banco de dados.

Relacionado: A folha de referências dos comandos SQL essenciais para iniciantes

Como acessar dados agrupados com uma função agregada

Você pode ter usado a função COUNT antes, principalmente na forma COUNT (*) , conforme visto acima. Ele busca o número de resultados em um conjunto. Você pode usá-lo para obter o número total de registros em uma tabela:

 SELECT COUNT(*) FROM albums
 +----------+
 | COUNT(*) |
 +----------+
 | 9 |
 +----------+

COUNT é uma função agregada. Este termo se refere a funções que convertem valores de várias linhas em um único valor. Eles geralmente são usados ​​em conjunto com a instrução GROUP BY.

Em vez de apenas contar o número de linhas, podemos aplicar uma função de agregação a valores agrupados:

 SELECT artist_id, SUM(sales)
 FROM albums
 GROUP BY artist_id
 +-----------+------------+
 | artist_id | SUM(sales) |
 +-----------+------------+
 | 1 | 14 |
 | 2 | 30 |
 | 3 | 28 |
 | 4 | 17 |
 | 5 | 8 |
 | 6 | 47 |
 | 7 | 28 |
 +-----------+------------+

As vendas totais mostradas acima para os artistas 2 e 6 são as vendas de seus vários álbuns combinadas:

 SELECT artist_id, sales
 FROM albums
 WHERE artist_id IN (2, 6)
 +-----------+-------+
 | artist_id | sales |
 +-----------+-------+
 | 2 | 24 |
 | 2 | 6 |
 | 6 | 25 |
 | 6 | 22 |
 +-----------+-------+

Agrupamento por múltiplas colunas

Você pode agrupar por mais de uma coluna. Basta incluir várias colunas ou expressões, separadas por vírgulas. Os resultados serão agrupados de acordo com a combinação dessas colunas.

 SELECT release_year, sales, count(*)
 FROM albums
 GROUP BY release_year, sales

Isso normalmente produzirá mais resultados do que agrupar por uma única coluna:

 +--------------+-------+----------+
 | release_year | sales | count(*) |
 +--------------+-------+----------+
 | 1969 | 14 | 1 |
 | 1973 | 24 | 1 |
 | 1977 | 28 | 2 |
 | 1991 | 17 | 1 |
 | 1977 | 6 | 1 |
 | 1973 | 8 | 1 |
 | 2011 | 25 | 1 |
 | 2015 | 22 | 1 |
 +--------------+-------+----------+

Observe que, em nosso pequeno exemplo, apenas dois álbuns têm o mesmo ano de lançamento e contagem de vendas (28 em 1977).

Funções de agregação úteis

Além de COUNT, várias funções funcionam bem com GROUP. Cada função retorna um valor com base nos registros pertencentes a cada grupo de resultados.

• COUNT () retorna o número total de registros correspondentes.
• SUM () retorna o total de todos os valores na coluna fornecida somados.
• MIN () retorna o menor valor em uma determinada coluna.
• MAX () retorna o maior valor em uma determinada coluna.
• AVG () retorna a média média. É o equivalente a SUM () / COUNT ().

Você também pode usar essas funções sem uma cláusula GROUP:

 SELECT AVG(sales) FROM albums
 +------------+
 | AVG(sales) |
 +------------+
 | 19.1111 |
 +------------+

Usando GROUP BY com uma cláusula WHERE

Assim como com um SELECT normal, você ainda pode usar WHERE para filtrar o conjunto de resultados:

 SELECT artist_id, COUNT(*)
 FROM albums
 WHERE release_year > 1990
 GROUP BY artist_id

 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 4 | 1 |
 | 6 | 2 |
 +-----------+----------+

Agora você tem apenas os álbuns lançados após 1990, agrupados por artista. Você também pode usar uma junção com a cláusula WHERE, independentemente do GROUP BY:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id

 +---------+--------+
 | name | albums |
 +---------+--------+
 | Nirvana | 1 |
 | Adele | 2 |
 +---------+--------+

Observe, no entanto, que se você tentar filtrar com base em uma coluna agregada:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND albums > 2
 GROUP BY artist_id;

Você obterá um erro:

 ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Colunas baseadas em dados agregados não estão disponíveis para a cláusula WHERE.

Usando a cláusula HAVING

Então, como você filtra o conjunto de resultados depois que um agrupamento ocorre? A cláusula HAVING lida com esta necessidade:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 GROUP BY artist_id
 HAVING albums > 1;

Observe que a cláusula HAVING vem depois de GROUP BY. Caso contrário, é essencialmente uma simples substituição de WHERE por HAVING. Os resultados são:

 +------------+--------+
 | name | albums |
 +------------+--------+
 | Pink Floyd | 2 |
 | Adele | 2 |
 +------------+--------+

Você ainda pode usar uma condição WHERE para filtrar os resultados antes do agrupamento. Funcionará junto com uma cláusula HAVING para filtragem após o agrupamento:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id
 HAVING albums > 1;

Apenas um artista em nosso banco de dados lançou mais de um álbum após 1990:

 +-------+--------+
 | name | albums |
 +-------+--------+
 | Adele | 2 |
 +-------+--------+

Combinando resultados com GROUP BY

A instrução GROUP BY é uma parte incrivelmente útil da linguagem SQL. Ele pode fornecer informações resumidas de dados, para uma página de conteúdo, por exemplo. É uma excelente alternativa para buscar grandes quantidades de dados. O banco de dados lida bem com essa carga de trabalho extra, pois seu próprio design o torna ideal para o trabalho.

Depois de entender o agrupamento e como unir várias tabelas, você poderá utilizar a maior parte do poder de um banco de dados relacional.