Como funciona o SQL GROUP BY?

O agrupamento de resultados é um poderoso recurso SQL que permite calcular as principais estatísticas para um grupo de registros.

GROUP BY é uma das cláusulas mais poderosas do SQL. Ele permite que você veja os dados de uma nova maneira e encontre métricas chave (como a média, valores máximos e mínimos em um grupo de registros).

Sem GROUP BY, todos os resultados que obtemos são orientados para os registros. Com GROUP BY, podemos criar grupos de registros e calcular métricas sobre cada grupo. Neste artigo, você aprenderá como o GROUP BY torna suas consultas SQL muito mais poderosas e diversificadas.

GROUP BY, Parte 1: Agrupamento de dados

Vamos supor que temos um pequeno hotel na Patagônia. Também temos um banco de dados contendo nomes dos hóspedes, cidades de origem, idades, datas de check-in, datas de check-out, e muito mais. Estes dados estão em duas tabelas chamadas room_guest e guest. Dê uma olhada:

room_guest

guest

Queremos calcular algumas estatísticas para que possamos reservar mais hóspedes. A cláusula SQL GROUP BY nos permite agrupar registros com base em dados em uma determinada coluna (ou colunas). Podemos agrupar os registros na tabela room_guest com base no valor da coluna origin_city. Então todos os registros de convidados de 'Gênova' pertencerão a um grupo; todos os registros de convidados de 'Dubai' pertencerão a outro grupo, e assim por diante. A tabela a seguir mostra cada grupo de registros em uma cor diferente.

Agora, suponha que o proprietário do hotel queira saber quantos hóspedes vêm de cada cidade. Para descobrir, precisamos contar o número de registros em cada grupo. Em outras palavras, precisamos da função agregada COUNT(*), que retorna o número de registros em um grupo. COUNT() é uma função muito comum; voltaremos a ela mais tarde neste artigo.

Portanto, precisamos de uma consulta para criar grupos de registros com o mesmo valor em origin_city e depois contar o número de registros em cada grupo. A consulta seria parecida com esta:

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

Você pode comparar o número de convidados de cada cidade na tabela de resultados abaixo com a tabela colorida mostrada anteriormente:

Observe que o número de linhas nos resultados da consulta é o mesmo que a quantidade de grupos criados pela cláusula GROUP BY. Um grupo para cada cidade, uma linha para cada cidade.

Para encerrar esta introdução a GROUP BY, gostaria de sugerir a leitura do artigo Getting the Hang of the GROUP BY Clause. Ele inclui uma descrição completa do GROUP BY e vários exemplos de seus erros mais comuns.

GROUP BY, Parte 2: Funções de Agregação

Embora o agrupamento por um valor seja útil, o verdadeiro poder de GROUP BY é quando é usado com funções agregadas. Eu diria até mesmo que cada consulta SQL usando uma cláusula GROUP BY deve ter pelo menos uma função agregada. (Mas não é obrigatório).

Na seção anterior, mencionamos que o GROUP BY é usado para criar grupos e calcular métricas. As métricas são calculadas por funções de agregação como COUNT(), SUM(), AVG(), MIN(), e MAX(). Os valores calculados por cada uma destas funções são auto-explicativos. Entretanto, todas elas têm algo em comum: todas as funções agregadas retornam um valor baseado em todos os registros do grupo.

Vamos considerar um exemplo. O proprietário do hotel quer saber o valor máximo faturado para cada quarto. Além disso, ele quer ver o valor mínimo e o valor médio faturado para cada quarto. Aqui está a consulta, seguida dos resultados:

SELECT 	room_number,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number

Agrupamento de Registros por Várias Colunas

Em alguns casos, podemos precisar agrupar por duas ou mais colunas. Podemos fazer isso com GROUP BY? Com certeza podemos!

Na consulta anterior, criamos um relatório analisando quanto dinheiro cada sala está gerando. Entretanto, algumas salas podem ser configuradas em um nível premium ou standard (ver sala número 2002) durante diferentes épocas; assim, para fazer uma análise correta, precisamos agrupar os registros usando duas colunas: room_number e room_level.

Antes de ir para a consulta, vamos usar cores para ver como os registros são agrupados pela cláusula GROUP BY room_number, room_level. Lembre-se de que os registros em cada grupo devem ter exatamente os mesmos valores tanto em room_number quanto em room_level. Por exemplo, o primeiro grupo é para room_number = 1001 e room_level = ‘standard’.

A consulta é:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number, room_level

A tabela a seguir mostra os resultados desta consulta. Você pode comparar esta tabela com a tabela anterior para verificar os resultados.

Agrupando valores NULL

Como qualquer outro valor, NULL valores têm seu próprio grupo; se tivermos um NULL em qualquer uma das colunas em GROUP BY, um grupo extra de registros é criado para esses registros. Para demonstrar isto, precisamos inserir um par de registros com valores NULL na coluna origin_city:

INSERT INTO into room_guest VALUES ('Kevin C.', NULL, 2001, '2019-07-25', '2019-08-07', NULL, 'standard', 10500);
INSERT INTO into room_guest VALUES  ('Karl J.', NULL, 1002, '2019-11-12', '2019-11-22', NULL, 'premium', 13900);

Em seguida, esta consulta ...

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

... mostrará o seguinte resultado. Observe o novo grupo para os valores NULL origin_city na primeira linha:

Usando WHERE com GROUP BY

A cláusula WHERE é freqüentemente utilizada nas consultas SQL, portanto é importante entender como funciona quando combinada com GROUP BY.

A cláusula WHERE é aplicada antes do GROUP BY. Isto significa que todos os registros são filtrados primeiro por WHERE; depois os registros que correspondem à condição WHERE são agrupados usando o critério GROUP BY.

Como exemplo, vamos usar a consulta anterior, mas desta vez vamos filtrar para convidados vindos das cidades de São Francisco e Los Angeles. A consulta é:

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest
WHERE		origin_city IN (‘San Francisco’,’Los Angeles’ )
GROUP BY	room_number, room_level

Como esperado, este conjunto de resultados é mais curto que os anteriores; a cláusula WHERE filtrou muitos hóspedes, e apenas os registros para quartos em São Francisco e Los Angeles foram processados pela cláusula GROUP BY.

Evitando problemas com o GROUP BY

Quando você está começando com GROUP BY, é comum se deparar com os seguintes problemas Veja aqui como evitá-los.

Problemas de contagem

Vejamos um caso semelhante em que precisamos acrescentar mais de uma coluna extra na cláusula GROUP BY. Na primeira consulta, agrupamos por origin_city. No entanto, algumas cidades compartilham o mesmo nome (porque estão em estados ou países diferentes). Em nosso conjunto de dados, temos duas cidades diferentes chamadas San Pedro, uma na Argentina e a outra na Espanha. Não queremos contá-las juntas, pois elas são dois lugares diferentes.

Para contar essas cidades separadamente, precisamos agrupar os registros usando as colunas city_origin, state, e country. Em seguida, repetiremos a primeira consulta, mas adicionaremos as colunas state e country à cláusula GROUP BY. Entretanto, se adicionarmos colunas ao GROUP BY, devemos adicioná-las também ao SELECT.

Porque as colunas indicam e o país estão na coluna guest tabela, temos que JOIN as tabelas room_guest e guest. Aqui está a pergunta que temos:

SELECT 	origin_city, state, country
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Os resultados mostram duas cidades "San Pedro" diferentes porque utilizamos state e country como colunas adicionais na cláusula GROUP BY.

Ainda há um problema a ser resolvido nesta consulta: se a mesma pessoa visitou o hotel duas vezes, estamos contando esta pessoa duas vezes. Isto não é necessariamente errado, mas e se quisermos saber o número de visitantes únicos ao hotel? Precisaríamos usar COUNT(distinct guest_name). A função de agrupamento COUNT(distinct column) retorna a quantidade de valores únicos para uma determinada coluna em um grupo de registros.

Na consulta abaixo, adicionamos a função COUNT(distinct). Também mantemos o COUNT(*) original para que o leitor possa comparar os dois resultados:

SELECT 	origin_city, state, country
COUNT(distinct guest_name) AS number_of_unique_guests,
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Agora podemos ver que o hotel recebeu um total de três visitas de um residente de Dubai, mas que estas três visitas foram feitas por duas pessoas distintas (Peter S. e Olek V) .

Antes de fechar esta seção, sugiro que assista a este vídeo de 5 minutos em GROUP BY para iniciantes. É uma maneira super dinâmica de aprender SQL.

Omitindo Colunas Não Agregadas de GROUP BY

Outro erro muito comum GROUP BY é adicionar uma coluna não agregada (ou seja, uma coluna que não é usada em uma função agregada) no SELECT que você não tem em GROUP BY. Para evitar este erro, siga uma regra muito simples: Todas as colunas em SELECT devem aparecer na cláusula GROUP BY ou ser usadas em uma função agregada.

Vamos tentar uma consulta inválida para ver o erro:

SELECT    room_number,
	    room_level,
	    origin_city, --This column is invalid, is not in the GROUP BY
    COUNT(*) AS quantity_of_visitors,
FROM      room_guest 
GROUP BY  room_number, room_level

Se executarmos esta consulta, obteremos o seguinte erro:

ERROR:  The column «room_guest.origin_city» must be in the GROUP BY clause
LINE 3:   guest_age,

Podemos corrigir o erro adicionando a coluna origin_city à cláusula GROUP BY:

	SELECT 	room_number,
			room_level,
			Origin_city,
			COUNT(*) AS quantity_of_visitors
	FROM		room_gest
	GROUP BY	room_number, room_level, origin_city -- origin_city added

Se você estiver tentando descobrir a diferença entre GROUP BY e ORDER BY, leia a Diferença entre GROUP BY e ORDER BY em Simple Words. Isso o ajudará a resolver o problema.

Há mais o que fazer com GROUP BY

Assim, aprendemos a utilizar GROUP BY para agrupar os registros por valores comuns. Conhecemos as funções agregadas MIN(), MAX(), AVG(), e SUM() computam várias estatísticas. E a função COUNT() faz um monte de coisas:

• COUNT(*) conta todas as filas.
• COUNT(guest_name) conta todos os valores não-NULL na coluna guest_name.
• COUNT(distinct guest_name) conta todos os diferentes valores não-NULL na coluna guest_name.

Ao agrupar, NULL obtém seu próprio grupo. E todas as colunas não agregadas em SELECT devem estar presentes em GROUP BY.

Devido à extensão do artigo, eu não cobri a cláusula HAVING, que é uma espécie de cláusula WHERE usada para filtrar grupos em vez de registros. Para aqueles leitores que querem ir um passo adiante, deixarei um link para o nosso SQL para Iniciantes curso, que cobre muitos tópicos interessantes. É uma ótima maneira de construir suas habilidades SQL!