Como usar GROUP BY e ORDER BY na mesma consulta: Um guia detalhado

Desvendando como usar duas cláusulas SQL básicas - GROUP BY e ORDER BY - em uma consulta.

GROUP BY e ORDER BY são cláusulas básicas do SQL. Como tal, elas são ensinadas relativamente cedo no processo de aprendizado. E geralmente não representam um grande obstáculo para os iniciantes. Entretanto, usar GROUP BY e ORDER BY juntos pode ser confuso. Até o final deste artigo, sua confusão sobre esse assunto desaparecerá.

Se você quiser praticar o uso de GROUP BY com ORDER BY, nosso curso interativo SQL para Iniciantes curso interativo o ajudará. Como o próprio nome diz, ele aborda os fundamentos do SQL. Isso facilita o acompanhamento dos tópicos que apresentaremos no artigo.

O curso em si tem 129 exercícios para praticar os conceitos básicos de SQL. Esses conceitos incluem extrair dados de uma ou várias tabelas (JOINs) e filtrar dados usando WHERE e operadores como LIKE, ILIKE, IN e BETWEEN. É claro que o GROUP BY está presente, assim como as operações de conjunto UNION, INTERSECT e EXCEPT.

O que são GROUP BY e ORDER BY no SQL?

GROUP BY e ORDER BY não são a mesma coisa - isso é óbvio!

A cláusula GROUP BY do SQL agrupa linhas de dados com os mesmos valores nas colunas especificadas. Essa cláusula é mais comumente usada com as funções de agregação do SQL para calcular estatísticas (como contagem de determinados valores, soma, média e o valor mínimo/máximo em um conjunto) para um grupo de linhas. Aqui está um exemplo simples:

SELECT 
  country,
  COUNT(*)
FROM box_office
GROUP BY country;

A consulta solicita ao banco de dados que agrupe os dados pelos valores da coluna country. Em seguida, ela mostra o nome do país e conta as linhas. Isso nos informará quantos registros da tabela pertencem a cada país.

Agora, vamos falar sobre ORDER BY. Esse comando classifica a saída da consulta em ordem crescente (1 a 10, A a Z) ou decrescente (10 a 1, Z a A). A classificação ascendente é o padrão; se você omitir a palavra-chave ASCending ou DESCending, a consulta será classificada em ordem ascendente. Você pode especificar a ordem de classificação usando ASC ou DESC. Aqui está um exemplo simples:

SELECT 
  movie,
  city,
  gross
FROM box_office
ORDER BY gross;

Essa consulta seleciona o nome do filme, a cidade onde o filme está sendo exibido e os ganhos brutos. Em seguida, a saída é classificada por ganhos brutos usando a cláusula ORDER BY.

Isso é algo que você provavelmente sabe. Agora, vamos ver como essas cláusulas SQL podem ser usadas em conjunto. Pode ser útil ter à mão este artigo sobre como funciona o GROUP BY ou o guia sobre ORDER BY, caso você precise de esclarecimentos sobre algo que mencionamos neste artigo.

Dados de exemplo

Usaremos uma tabela chamada box_office nos exemplos a seguir. Você pode criá-la usando esta consulta.

Trata-se de uma visão geral dos ganhos de bilheteria de dois filmes: "Beau Is Afraid" e "The French Dispatch".

Os dados mostram os ganhos (em dólares americanos) de cinemas em dois países - Espanha e Holanda - e suas respectivas cidades: Amsterdã, Roterdã, Den Haag, Barcelona, Madri e Valência.

GROUP BY e ORDER BY em uma coluna não agregada no SELECT

Vamos começar com o exemplo mais fácil de usar GROUP BY e ORDER BY em uma consulta. Queremos mostrar a lista de filmes em ordem alfabética.

Veja como agrupar por uma coluna e ordenar o resultado por uma coluna:

SELECT movie
FROM box_office
GROUP BY movie
ORDER BY movie ASC; 

A consulta seleciona o nome do filme na tabela box_office. Em seguida, precisamos usar GROUP BY. Sem isso, o resultado seria todas as doze linhas da tabela, mostrando os nomes repetidos dos mesmos dois filmes da tabela box_office. Queremos que cada filme apareça apenas uma vez, pois esse é o objetivo da lista. O agrupamento de dados é feito simplesmente especificando a coluna pela qual você deseja agrupar na cláusula GROUP BY.

A saída é ordenada usando a cláusula ORDER BY. Assim como em GROUP BY, especificamos o nome da coluna na cláusula. Queremos que o resultado seja classificado em ordem alfabética, portanto, o nome da coluna é seguido por ASC.

Vamos usar essa imagem da ordem de execução da consulta SQL para visualizar como o banco de dados chega ao resultado.

A consulta primeiro obtém a tabela na cláusula FROM. Em seguida, agrupa os dados de acordo com a coluna especificada em GROUP BY. Depois disso, ela seleciona a coluna na instrução SELECT. E só depois disso tudo é que o resultado é classificado pela cláusula ORDER BY.

Em outras palavras, os dados são primeiro agrupados e depois classificados. Isso nos leva a uma observação importante: ao usar GROUP BY e ORDER BY em uma consulta, GROUP BY deve sempre, sem exceção, ser escrito antes de ORDER BY!

Vamos dar uma olhada no resultado da consulta.

Como você pode ver, é uma lista que mostra nossos dois filmes em ordem alfabética.

GROUP BY e ORDER BY em uma coluna agregada no SELECT

Você pode usar GROUP BY junto com ORDER BY ao fazer o pedido por uma função agregada mostrada em SELECT. Veja um exemplo:

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY gross_per_movie DESC;

A consulta seleciona um filme da tabela. Em seguida, usamos a função de agregação SUM() para somar os ganhos brutos do filme. Como queremos mostrar os ganhos brutos por filme, precisamos agrupar o resultado por nome do filme. Como etapa final, queremos classificar o resultado dos ganhos mais altos para os mais baixos. Podemos fazer isso listando a coluna agregada em ORDER BY. Isso deve ser seguido pela palavra-chave DESC para classificar do maior para o menor valor.

Observe que usamos o alias gross_per_movie para a coluna agregada em ORDER BY. Também poderíamos ter usado a expressão de agregação completa:

SELECT 
  movie,
  SUM(gross) AS gross_per_movie
FROM box_office
GROUP BY movie
ORDER BY SUM(gross) DESC;

Veja a seguir a aparência do resultado:

Ele mostra dois filmes e seus ganhos em ordem decrescente.

GROUP BY e ORDER BY em uma coluna não agregada não em SELECT

No primeiro exemplo, ordenamos o resultado pela coluna mostrada em SELECT. Vamos agora tentar ordenar os dados por uma coluna que não está em SELECT:

SELECT SUM(gross) AS gross_by_city
FROM box_office
GROUP BY city
ORDER BY city ASC;

Nessa consulta, usamos a função SUM() para calcular os ganhos brutos por cidade. Entretanto, não há nenhuma coluna de cidade em SELECT. Isso porque queremos ver apenas os valores brutos. Ainda assim, agrupamos o resultado por cidade, da mesma forma que faríamos se essa coluna estivesse em SELECT. Por fim, ordenamos o resultado em ordem alfabética por cidade.

O resultado é mostrado abaixo:

Como você pode ver, a consulta funciona. Lembre-se: Você pode ordenar o resultado por uma coluna não agregada não mostrada em SELECT - mas somente se essa coluna estiver listada em GROUP BY.

Isso significa que os dados também podem ser agrupados por uma coluna não mostrada em SELECT. A coluna pode estar listada em SELECT, mas não em GROUP BY? Não! Você deve se lembrar desta regra: Se a coluna não agregada aparecer em SELECT, ela também deve aparecer em GROUP BY.

GROUP BY e ORDER BY em uma coluna agregada que não está em SELECT

Vamos ajustar um pouco o exemplo acima e tentar ordenar os dados por uma coluna agregada não listada no site SELECT.

Aqui está o código. Ele tenta mostrar uma lista de cidades ordenadas por ganhos brutos:

SELECT city
FROM box_office
GROUP BY city
ORDER BY SUM(gross);

Selecionamos a coluna city e agrupamos a saída por ela.

Em seguida, usamos SUM() em ORDER BY para que possamos classificar a saída pelos ganhos por cidade. Classificamos em ordem crescente.

Vamos ver se a consulta funciona:

Sim, funciona! Outra observação: você pode ordenar o resultado por uma coluna agregada não mostrada no arquivo SELECT.

GROUP BY e ORDER BY Multiple Columns (Agrupar por e ordenar por várias colunas)

Até agora, agrupamos os dados por apenas uma coluna. Vamos ver como você pode agrupar dados por mais de uma coluna.

Vamos mostrar cada cidade e seu país:

SELECT 
  city,
  country,
  AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY city;

Selecionamos a cidade e o país. Depois disso, usamos o site AVG() para calcular a média de ganhos brutos por cidade. Em seguida, listamos as mesmas colunas em GROUP BY. Como você aprendeu, cada coluna que aparece em SELECT também deve aparecer em GROUP BY. Quando listamos várias colunas em GROUP BY, simplesmente as separamos com uma vírgula.

Por fim, classificamos o resultado por cidade em ordem crescente:

Isso levanta a questão: Se você pode GROUP BY várias colunas, você também pode ORDER BY mais de uma coluna?

Vamos tentar:

SELECT city,
	 country,
	 AVG(gross) AS average_gross
FROM box_office
GROUP BY city, country
ORDER BY country, city;

O princípio é o mesmo que em GROUP BY: basta listar as colunas em ORDER BY e separá-las por vírgula.

Aqui está o que a consulta retorna:

Sim, o resultado é realmente ordenado pelo país e depois por cada cidade dentro do país.

Coluna agregada em GROUP BY e ORDER BY

Tentamos todas as combinações, mas não tentamos GROUP BY e ORDER BY várias colunas, das quais uma é agregada. Vamos tentar isso:

SELECT 
  city,
  AVG(gross) AS average_gross_by_city
FROM box_office
GROUP BY city, AVG(gross)
ORDER BY city, AVG(gross);

Esta consulta quer mostrar o ganho bruto médio por cidade. Listamos a cidade em SELECT e usamos a função AVG(). Em seguida, agrupamos por cidade e pela coluna agregada. Por fim, o resultado é classificado pela mesma coluna.

No entanto, o único resultado que obtemos é este erro.

A mensagem de erro significa literalmente o que diz: funções de agregação não são permitidas em GROUP BY. Qual seria o objetivo de usá-las em GROUP BY? A função aggregate agrega dados em uma linha e o GROUP BY não pode fazer mais nada com ela.

Uso de GROUP BY com HAVING e ORDER BY

GROUP BY e ORDER BY não são as únicas cláusulas que podem ser usadas em uma consulta - também podemos adicionar HAVING.

Vamos inspecionar o código a seguir para ver como isso funciona.

SELECT 
  city,
  SUM(gross) AS gross_per_city
FROM box_office
GROUP BY city
HAVING SUM(gross) > 800000
ORDER BY SUM(gross) > 800000 DESC;

Essa consulta deve:

• Retornar cidades com ganhos brutos acima de US$ 800.000.
• Mostrar o valor ganho por essa cidade.
• Ordenar o resultado por ganho bruto em ordem decrescente.

Para fazer isso, selecionamos a coluna city e usamos a função SUM(). Em seguida, agrupamos o resultado por cidade. Depois disso, usamos a cláusula HAVING para mostrar somente as cidades com ganhos acima de US$ 800.000. Simplesmente usamos a coluna agregada de SELECT e a comparamos com a condição usando o operador de comparação ">". Em seguida, usamos o mesmo em ORDER BY para classificar o resultado de forma decrescente.

A regra aqui é: A cláusula HAVING é sempre escrita depois de GROUP BY mas antes de ORDER BY.

Você pode revisitar a imagem da ordem de execução da consulta SQL para verificar isso. Você verá que HAVING é executado depois de GROUP BY, mas antes de ORDER BY. Em outras palavras, os dados são primeiro agrupados, depois filtrados e, por fim, ordenados.

Aqui está o resultado da consulta:

Você vê que há apenas duas cidades onde nossos dois filmes ganharam mais de US$ 800.000: Amsterdã e Madri.

Praticar o uso de GROUP BY com ORDER BY

Este artigo mostrou a você que, sim, GROUP BY e ORDER BY podem ser usados na mesma consulta SQL. E não é só isso, eles podem ser usados de várias maneiras.

Ao longo do caminho, aprendemos algumas regras importantes:

1. GROUP BY vem antes de ORDER BY.
2. A saída pode ser ordenada por uma coluna não agregada não mostrada em SELECT somente se a coluna aparecer em GROUP BY.
3. Os dados podem ser agrupados pela coluna não agregada não mostr ada em SELECT.
4. Qualquer coluna não agregada que apareça em SELECT deve ser listada em GROUP BY.
5. A saída pode ser ordenada por uma coluna agregada não mostr ada em SELECT.
6. Uma coluna agregada não é permitida em GROUP BY.
7. HAVING vem depois de GROUP BY, mas antes de ORDER BY.

Embora tenhamos explicado essas regras e você sinta que aprendeu alguma coisa, isso não é suficiente.

Para reter esse conhecimento, você precisa praticar o uso de GROUP BY e ORDER BY, especialmente no início de sua jornada SQL. A prática em SQL significa escrever muito código. Essa é a única maneira de consolidar seu conhecimento. Nosso curso interativo SQL para Iniciantes oferece 129 exercícios práticos de SQL; é muito espaço para praticar!