A cláusula group by agrupa as linhas de um resultado em grupos.
group by é frequentemente usada com funções como count(), min(), max(), sum() e avg() para encontrar propriedades de grupos, como o número de elementos (count), o total de valores (sum), o elemento máximo (max), o elemento mínimo (min) e a média dos elementos (avg). Chamamos a essas funções de funções de agregação por serem funções que trabalham dando resultados a um conjunto(agregação) de itens.
Sintaxe de Uso do GROU BY
O seguinte ilustra a sintaxe da cláusula group by:
select
lista_campos
from
nome_tabela
group by
coluna1, coluna2,...;
Nesta sintaxe, a cláusula group by agrupa linhas em grupos e a função agregada é aplicada a cada grupo para retornar a linha de resumo.
Tabelas que Usaremos nos Exemplos
Usaremos as tabelas de countries e regions do banco de dados nation. Esse é um banco que baixamos nesse artigo: MariaDB e MySQL: Baixando Exemplo de Banco de Dados.
Estrutura da Tabela countries:
MariaDB [nation]> desc countries; +---------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+----------------+ | country_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | area | decimal(10,2) | NO | | NULL | | | national_day | date | YES | | NULL | | | country_code2 | char(2) | NO | UNI | NULL | | | country_code3 | char(3) | NO | UNI | NULL | | | region_id | int(11) | NO | MUL | NULL | | +---------------+---------------+------+-----+---------+----------------+
Estrutura da Tabela regions:
MariaDB [nation]> desc regions; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | region_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | continent_id | int(11) | NO | MUL | NULL | | +--------------+--------------+------+-----+---------+----------------+
Exemplos SQL Usando GROUP BY
#1 usando group by com a função count() para obter o número de países em cada região. Exemplo, na região 1 temos 24 países, na 2 temos 14….
select region_id, count(country_id) from countries group by region_id;
MariaDB [nation]> select region_id, count(country_id) from countries group by region_id; +-----------+-------------------+ | region_id | count(country_id) | +-----------+-------------------+ | 1 | 24 | | 2 | 14 | | 3 | 9 | | 4 | 15 | | 5 | 18 | | 6 | 14 | | 7 | 10 | | 8 | 5 | | 9 | 5 | | 10 | 9 | | 11 | 20 | | 12 | 17 | | 13 | 10 | | 14 | 8 | | 15 | 5 | | 16 | 11 | | 17 | 5 | | 18 | 8 | | 19 | 7 | | 20 | 7 | | 21 | 3 | | 22 | 5 | | 23 | 7 | | 24 | 2 | | 25 | 1 | +-----------+-------------------+
Acima o group by divide os países por regiões e então a função count() é aplicada a cada região para retornar o número de países.
#2 Acima ficou um pouco confuso por haver apenas números. Para tornar o resultado mais significativo, você pode usar join para unir a tabela countries à tabela de regions.
select regions.name, count(country_id) from countries inner join regions
using(region_id) group by regions.name;
MariaDB [nation]> select regions.name, count(country_id) from countries inner join regions using(region_id) group by regions.name; +---------------------------+-------------------+ | name | count(country_id) | +---------------------------+-------------------+ | Antarctica | 5 | | Australia and New Zealand | 5 | | Baltic Countries | 3 | | British Islands | 2 | | Caribbean | 24 | | Central Africa | 9 | | Central America | 8 | | Eastern Africa | 20 | | Eastern Asia | 8 | | Eastern Europe | 10 | | Melanesia | 5 | | Micronesia | 7 | | Micronesia/Caribbean | 1 | | Middle East | 18 | | Nordic Countries | 7 | | North America | 5 | | Northern Africa | 7 | | Polynesia | 10 | | South America | 14 | | Southeast Asia | 11 | | Southern Africa | 5 | | Southern and Central Asia | 14 | | Southern Europe | 15 | | Western Africa | 17 | | Western Europe | 9 | +---------------------------+-------------------+
#3 podemos usar a função sum() para somar. Abaixo usamos sum() para calcular a área total de países em cada região.
select regions.name, sum(area) from countries inner join regions using(region_id) group by regions.name order by area desc;
MariaDB [nation]> select regions.name, sum(area) from countries inner join regions using(region_id) group by regions.name order by area desc; +---------------------------+-------------+ | name | sum(area) | +---------------------------+-------------+ | Antarctica | 13132101.00 | | Eastern Asia | 11774482.00 | | Australia and New Zealand | 8011939.00 | | South America | 17864926.00 | | Northern Africa | 8524703.00 | | Central Africa | 6612667.00 | | Southern and Central Asia | 10791130.00 | | Southern Africa | 2674778.00 | | British Islands | 313173.00 | | Western Africa | 6138338.00 | | Eastern Europe | 18814094.00 | | Western Europe | 1108456.50 | | Middle East | 4820592.00 | | Baltic Countries | 175117.00 | | Nordic Countries | 1321901.00 | | Southern Europe | 1316392.40 | | Eastern Africa | 6299891.00 | | Central America | 2479532.00 | | Melanesia | 540774.00 | | Southeast Asia | 4494801.00 | | Micronesia | 3102.00 | | Polynesia | 8463.00 | | Caribbean | 234423.00 | | North America | 21500515.00 | | Micronesia/Caribbean | 16.00 | +---------------------------+-------------+
#4 aqui usaremos group by com a função min() para encontrar as menores áreas dos países em cada região
select regions.name, min(area) from countries inner join regions using(region_id) group by regions.name order by regions.name;
MariaDB [nation]> select regions.name, min(area) from countries inner join regions using(region_id) group by regions.name order by regions.name; +---------------------------+-----------+ | name | min(area) | +---------------------------+-----------+ | Antarctica | 59.00 | | Australia and New Zealand | 14.00 | | Baltic Countries | 45227.00 | | British Islands | 70273.00 | | Caribbean | 96.00 | | Central Africa | 964.00 | | Central America | 21041.00 | | Eastern Africa | 78.00 | | Eastern Asia | 18.00 | | Eastern Europe | 33851.00 | | Melanesia | 12189.00 | | Micronesia | 21.00 | | Micronesia/Caribbean | 16.00 | | Middle East | 694.00 | | Nordic Countries | 1399.00 | | North America | 53.00 | | Northern Africa | 163610.00 | | Polynesia | 12.00 | | South America | 12173.00 | | Southeast Asia | 618.00 | | Southern Africa | 17364.00 | | Southern and Central Asia | 298.00 | | Southern Europe | 0.40 | | Western Africa | 314.00 | | Western Europe | 1.50 | +---------------------------+-----------+
#5 agora veremos quais as maiores áreas em cada região
select regions.name, max(area) from countries inner join regions using(region_id) group by regions.name order by regions.name;
MariaDB [nation]> select regions.name, max(area) from countries inner join regions using(region_id) group by regions.name order by regions.name; +---------------------------+-------------+ | name | max(area) | +---------------------------+-------------+ | Antarctica | 13120000.00 | | Australia and New Zealand | 7741220.00 | | Baltic Countries | 65301.00 | | British Islands | 242900.00 | | Caribbean | 110861.00 | | Central Africa | 2344858.00 | | Central America | 1958201.00 | | Eastern Africa | 1104300.00 | | Eastern Asia | 9572900.00 | | Eastern Europe | 17075400.00 | | Melanesia | 462840.00 | | Micronesia | 726.00 | | Micronesia/Caribbean | 16.00 | | Middle East | 2149690.00 | | Nordic Countries | 449964.00 | | North America | 9970610.00 | | Northern Africa | 2505813.00 | | Polynesia | 4000.00 | | South America | 8547403.00 | | Southeast Asia | 1904569.00 | | Southern Africa | 1221037.00 | | Southern and Central Asia | 3287263.00 | | Southern Europe | 505992.00 | | Western Africa | 1267000.00 | | Western Europe | 551500.00 | +---------------------------+-------------+
#6 vamos ver agora qual a área média de cada região, para isso usaremos a função avg().
select regions.name, avg(area) from countries inner join regions using(region_id) group by regions.name order by area desc;
MariaDB [nation]> select regions.name, avg(area) from countries inner join regions using(region_id) group by regions.name order by area desc; +---------------------------+----------------+ | name | avg(area) | +---------------------------+----------------+ | Antarctica | 2626420.200000 | | Eastern Asia | 1471810.250000 | | Australia and New Zealand | 1602387.800000 | | South America | 1276066.142857 | | Northern Africa | 1217814.714286 | | Central Africa | 734740.777778 | | Southern and Central Asia | 770795.000000 | | Southern Africa | 534955.600000 | | British Islands | 156586.500000 | | Western Africa | 361078.705882 | | Eastern Europe | 1881409.400000 | | Western Europe | 123161.833333 | | Middle East | 267810.666667 | | Baltic Countries | 58372.333333 | | Nordic Countries | 188843.000000 | | Southern Europe | 87759.493333 | | Eastern Africa | 314994.550000 | | Central America | 309941.500000 | | Melanesia | 108154.800000 | | Southeast Asia | 408618.272727 | | Micronesia | 443.142857 | | Polynesia | 846.300000 | | Caribbean | 9767.625000 | | North America | 4300103.000000 | | Micronesia/Caribbean | 16.000000 | +---------------------------+----------------+