Como Usar GROUP BY e ORDER BY no SQL

Introdução

Bancos de dados em Linguagem de Consulta Estruturada (SQL) podem armazenar e gerenciar muitos dados em várias tabelas. Com conjuntos de dados grandes, é importante entender como ordenar os dados, especialmente para analisar conjuntos de resultados ou organizar dados para relatórios ou comunicações externas.

Dois comandos comuns em SQL que ajudam a ordenar seus dados são GROUP BY e ORDER BY. Um comando GROUP BY ordena os dados agrupando-os com base na(s) coluna(s) que você especifica na consulta e é usado com funções de agregação. Um ORDER BY permite que você organize conjuntos de resultados em ordem alfabética ou numérica e em ordem ascendente ou descendente.

Neste tutorial, você irá ordenar os resultados da consulta em SQL usando os comandos GROUP BY e ORDER BY. Você também praticará a implementação de funções de agregação e a cláusula WHERE em suas consultas para ordenar ainda mais os resultados.

Pré-requisitos

Para seguir este guia, você precisará de um computador executando algum tipo de sistema de gerenciamento de banco de dados relacional (RDBMS) que utilize SQL. As instruções e exemplos neste tutorial foram validados usando o seguinte ambiente:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL instalado e seguro no servidor. Siga nosso guia Como Instalar o MySQL no Ubuntu 20.04 para configurar isso. Este guia pressupõe que você também configurou um usuário MySQL não root, conforme descrito no Passo 3 deste guia.

Para praticar a ordenação dos resultados de dados neste tutorial, você precisará de um banco de dados e tabela carregados com dados de exemplo. Se você não tiver um pronto para inserir, você pode ler a seguinte seção Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra para aprender como criar um banco de dados e tabela. Este tutorial fará referência a este banco de dados e tabela de amostra ao longo dele.

Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra

Se seu banco de dados SQL é executado em um servidor remoto, faça login via SSH no seu servidor a partir da sua máquina local:

ssh sammy@your_server_ip

Em seguida, abra o prompt do MySQL, substituindo sammy pelas informações da sua conta de usuário MySQL:

mysql -u sammy -p

Crie um banco de dados chamado movieDB:

CREATE DATABASE movieDB;

Se o banco de dados for criado com sucesso, você receberá a seguinte saída:

Output
Query OK, 1 row affected (0.01 sec)

Para selecionar o banco de dados movieDB, execute a seguinte instrução USE:

USE movieDB;

Output
Database changed

Após selecionar o banco de dados, crie uma tabela dentro dele. Para o exemplo deste tutorial, criaremos uma tabela que armazena informações sobre as exibições de um cinema local. Esta tabela terá as seguintes sete colunas:

• theater_id: armazena valores do tipo de dados int para os salões de exibição de cada cinema e servirá como chave primária da tabela, significando que cada valor nesta coluna funcionará como um identificador único para sua respectiva linha.
• date: usa o tipo de dados DATE para armazenar a data específica em que um filme foi exibido, ano, mês e dia (AAAA-MM-DD). Este tipo de dados adere aos seguintes parâmetros: quatro dígitos para o ano e no máximo dois dígitos para o mês e dia.
• time: representa a exibição programada do filme com o tipo de dados TIME, horas, minutos e segundos (HH:MM:SS).
• movie_name: armazena o nome do filme usando o tipo de dados varchar com um máximo de 40 caracteres.
• movie_genre: utiliza o tipo de dados varchar com um máximo de 30 caracteres, para armazenar informações sobre o gênero de cada filme.
• guest_total: mostra o número total de convidados que compareceram a uma sessão de filme com o tipo de dados int.
• ticket_cost: utiliza o tipo de dados decimal, com uma precisão de quatro e uma escala de um, significando que os valores nesta coluna podem ter quatro dígitos e dois dígitos à direita do ponto decimal. Esta coluna representa o custo do ingresso para a sessão de filme específica.

Crie uma tabela chamada movie_theater que contenha cada uma dessas colunas executando o seguinte comando CREATE TABLE:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

Em seguida, insira alguns dados de exemplo na tabela vazia:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Depois de inserir os dados, você está pronto para começar a classificar os resultados da consulta em SQL.

Usando GROUP BY

A função de uma declaração GROUP BY é agrupar registros com valores compartilhados. Uma declaração GROUP BY é sempre usada com uma função de agregação em uma consulta. Como você pode lembrar, uma função de agregação resume informações e retorna um único resultado. Por exemplo, você pode consultar a contagem total ou a soma de uma coluna e isso produzirá um único valor em seu resultado. Com uma cláusula GROUP BY, você pode implementar a função de agregação para obter um valor de resultado para cada grupo desejado.

GROUP BY é útil para retornar vários resultados desejados ordenados pelos grupos especificados, em vez de apenas uma coluna. Além disso, GROUP BY sempre deve vir depois da declaração FROM e da cláusula WHERE, se você optar por usá-la. Aqui está um exemplo de como uma consulta com GROUP BY e função de agregação é estruturada:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

Para ilustrar como você pode usar declarações GROUP BY, digamos que você está liderando a campanha para vários lançamentos de filmes, e você quer avaliar o sucesso dos seus esforços de marketing. Você pede a um cinema local para compartilhar os dados que eles coletaram dos convidados na sexta-feira e no sábado. Comece revisando os dados executando SELECT e o símbolo * para selecionar “cada coluna” da tabela movie_theater:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

Embora esses dados sejam úteis, você quer realizar uma avaliação mais profunda e ordenar os resultados para algumas colunas específicas.

Como você trabalhou em filmes de alguns gêneros diferentes, você está interessado em saber o quão bem eles foram recebidos pelos espectadores. Especificamente, você quer saber a quantidade média de pessoas que assistiram a cada gênero de filme. Use SELECT para recuperar os vários tipos de filmes da coluna movie_genre. Em seguida, aplique a função de agregação AVG na coluna guest_total, use AS para criar um alias para uma coluna chamada average, e inclua a declaração GROUP BY para agrupar os resultados por movie_genre. Agrupando-os dessa forma, você terá os resultados médios para cada gênero de filme:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

Esta saída fornece as quatro médias para cada gênero dentro do grupo movie_genre. Com base nessas informações, os filmes de Ação atraíram o maior número médio de convidados por exibição.

Em seguida, digamos que você queira medir as receitas do teatro ao longo de dois dias separados. A seguinte consulta retorna valores da coluna date, bem como valores retornados pela função agregada SUM. Especificamente, a função agregada SUM irá envolver uma equação matemática entre parênteses para multiplicar (usando o operador *) o número total de convidados pelo custo de um ingresso, representado como: SUM(guest_total * ticket_cost). Esta consulta inclui a cláusula AS para fornecer o alias total_revenue para a coluna retornada pela função agregada. Em seguida, complete a consulta com a instrução GROUP BY para agrupar os resultados da consulta pela coluna date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Já que você usou GROUP BY para agrupar a coluna date, sua saída fornece os resultados para a receita total em vendas de ingressos para cada dia, neste caso, $7,272 para sexta-feira, 27 de maio, e $9,646 para sábado, 28 de maio.

Agora imagine que você queira focar e analisar um filme: Os Malvados. Neste cenário, você deseja descobrir como o timing e os pontos de preço impactam a escolha de uma família para assistir a um filme de animação. Para essa consulta, use a função de agregação MAX para recuperar o máximo ticket_cost, garantindo incluir AS para criar o alias para a coluna price_data. Em seguida, use a cláusula WHERE para restringir os resultados pelo movie_name exclusivamente para “Os Malvados”, e use AND também para determinar os horários de filme mais populares com base nos números de guest_total que foram mais de 100 com o operador de comparação >. Em seguida, conclua a consulta com a instrução GROUP BY e agrupe-a por time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

De acordo com esta saída, mais convidados compareceram ao filme Os Malvados no horário matinal das 9:00, que tinha o ponto de preço mais acessível de $8.00 por ingresso. No entanto, esses resultados também mostram que os convidados do filme pagaram o preço do ingresso mais alto de $13.00 às 17:00, sugerindo que as famílias preferem sessões que não sejam muito tarde no dia e pagarão um pouco mais pelo ingresso. Isso parece ser uma avaliação justa quando comparado ao horário das 22:00, quando o filme Os Malvados teve apenas 83 convidados e o preço por ingresso era de $18.00. Essas informações podem ser úteis para fornecer ao gerente do cinema evidências de que abrir mais horários matinais e noturnos pode aumentar a participação das famílias que estão fazendo uma escolha com base em um horário e ponto de preço preferidos.

Por favor, note que embora GROUP BY seja quase sempre usado com uma função de agregação, pode haver exceções, embora improváveis. No entanto, se você quiser agrupar seus resultados sem uma função de agregação, você pode usar a declaração DISTINCT para obter o mesmo resultado. A cláusula DISTINCT remove quaisquer duplicatas em um conjunto de resultados retornando os valores únicos na coluna, e só pode ser usada com uma declaração SELECT. Por exemplo, se você quisesse agrupar todos os filmes juntos pelo nome, você poderia fazer isso com a seguinte consulta:

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

Como você se lembra de ver todos os dados na tabela, havia duplicatas dos nomes dos filmes, pois havia várias exibições. Portanto, DISTINCT removeu essas duplicatas e efetivamente agrupou os valores únicos sob a única coluna nome_do_filme. Isso é efetivamente idêntico à seguinte consulta, que inclui uma declaração GROUP BY:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Agora que você praticou o uso do GROUP BY com funções de agregação, em seguida, você aprenderá como ordenar os resultados da sua consulta com a declaração ORDER BY.

USANDO ORDER BY

A função da instrução ORDER BY é classificar os resultados em ordem ascendente ou descendente com base na(s) coluna(s) que você especifica na consulta. Dependendo do tipo de dados armazenados pela coluna que você especifica após ela, o ORDER BY irá organizá-los em ordem alfabética ou numérica. Por padrão, o ORDER BY classificará os resultados em ordem ascendente; no entanto, se preferir ordem descendente, você deve incluir a palavra-chave DESC em sua consulta. Você também pode usar a instrução ORDER BY com GROUP BY, mas ela deve vir depois para funcionar corretamente. Semelhante ao GROUP BY, o ORDER BY também deve vir depois da instrução FROM e da cláusula WHERE. A sintaxe geral para usar ORDER BY é a seguinte:

SELECT column_1, column_2 FROM table ORDER BY column_1;

Vamos continuar com os dados de exemplo para o cinema e praticar a ordenação de resultados com ORDER BY. Comece com a seguinte consulta que recupera valores da coluna guest_total e organiza esses valores numéricos com uma instrução ORDER BY:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

Como sua consulta especificou uma coluna com valores numéricos, a instrução ORDER BY organizou os resultados em ordem numérica e ascendente, começando com 25 sob a coluna guest_total.

Se você preferir ordenar a coluna em ordem decrescente, você adicionaria a palavra-chave DESC ao final da consulta. Além disso, se você quiser ordenar os dados pelos valores de caractere sob movie_name, você especificaria isso na sua consulta. Vamos realizar esse tipo de consulta usando ORDER BY para ordenar a coluna movie_name com valores de caractere em ordem decrescente. Ordene os resultados ainda mais incluindo uma cláusula WHERE para recuperar os dados sobre filmes exibidos às 22:00 da coluna time:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

Este conjunto de resultados lista as quatro diferentes exibições de filmes às 22:00 em ordem alfabética decrescente, começando por Top Gun Maverick até Downtown Abbey A New Era.

Para esta próxima consulta, combine as declarações ORDER BY e GROUP BY com a função de agregação SUM para gerar resultados sobre a receita total recebida para cada filme. No entanto, vamos dizer que o cinema contou errado o número total de convidados e esqueceu de incluir festas especiais que tinham ingressos pré-comprados e reservados para um grupo de 12 pessoas em cada exibição.

Nesta consulta, utilize o SUM e inclua os 12 convidados adicionais em cada exibição de filme, implementando o operador de adição + e então adicionando 12 ao guest_total. Certifique-se de envolver isso entre parênteses. Em seguida, multiplique esse total pelo ticket_cost com o operador *, e complete a equação matemática fechando o parêntese no final. Adicione a cláusula AS para criar o alias para a nova coluna intitulada total_revenue. Em seguida, use o GROUP BY para agrupar os resultados de total_revenue para cada filme com base nos dados obtidos na coluna movie_name. Por último, use o ORDER BY para organizar os resultados sob a nova coluna total_revenue em ordem ascendente:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

Este conjunto de resultados nos informa a receita total para cada filme com as vendas de ingressos adicionais de 12 convidados e organiza as vendas totais de ingressos em ordem ascendente, do menor para o maior. A partir disso, aprendemos que Top Gun Maverick recebeu as vendas de ingressos mais altas, enquanto Men recebeu as menores. Enquanto isso, os filmes The Bad Guys e Downton Abbey A New Era foram muito próximos em vendas totais de ingressos.

Nesta seção, você praticou várias maneiras de implementar a instrução ORDER BY e como especificar a ordem que você preferir, como ordens ascendente e descendente para ambos os valores de dados numéricos e de caracteres. Você também aprendeu como incluir a cláusula WHERE para reduzir seus resultados e realizou uma consulta usando tanto as instruções GROUP BY quanto ORDER BY com uma função de agregação e equação matemática.

Conclusão

Entender como usar as declarações GROUP BY e ORDER BY é importante para classificar seus resultados e dados. Quer você queira organizar vários resultados sob um grupo, organizar uma de suas colunas em ordem alfabética e decrescente, ou fazer ambos simultaneamente; cabe a você e ao(s) resultado(s) desejado(s). Você também aprendeu sobre outras maneiras de classificar seus resultados ainda mais com a cláusula WHERE. Se você quiser aprender mais, confira nosso tutorial sobre Como Usar Coringas em SQL para praticar filtrar resultados com a cláusula LIKE.