Obtendo a Forca do GRUPO POR Cláusula

GROUP BY é uma parte importante da instrução SQL SELECT. Mas novos codificadores SQL podem encontrar alguns problemas quando esta cláusula é usada de forma incorreta. Veja aqui como evitar esses problemas.

Você está aprendendo SQL. Você sabe como SELECIONAR alguns dados de uma tabela e como filtrar os dados com uma cláusula WHERE. Você pode processar os dados usando funções agregadas (MIN, MAX, SUM, AVG, e outras). Mas quando você estiver lidando com muitos dados, talvez precise estreitá-los ainda mais. É aí que entra a cláusula GROUP BY - ela permite que você organize as informações com base nos parâmetros que você definir.

Neste artigo, vamos explicar como você pode usar GROUP BY. Também vamos falar sobre alguns possíveis problemas e como evitá-los ou corrigi-los.

Usando GROUP BY

Para entender como usar GROUP BY, precisamos primeiro definir uma tabela para praticar. Que tal uma que descreva as pessoas?

| ID | Name     | Gender | Height | Weight | Eye_color |
--------------------------------------------------------
|  1 | Mark     | Male   |    180 |     78 | Blue      |
|  2 | Susan    | Female |    172 |     59 | Brown     |
|  3 | Thomas   | Male   |    179 |     92 | Brown     |
|  4 | Katarina | Female |    164 |     53 | Green     |
|  5 | Mindy    | Female |    170 |     58 | Blue      |
--------------------------------------------------------

Então, temos nossa tabela de prática. Agora entramos nas estatísticas. Por exemplo, qual é a altura média de todo o nosso pessoal? Para descobrir, nós digitamos:

SELECT AVG(Height)
FROM People

+-------------+
| AVG(Height) |
+-------------+
|    173.0000 |
+-------------+

Agora suponha que queremos saber a altura média por gênero. Isso parece ser bastante fácil; simplesmente acrescentamos uma cláusula WHERE. Então, nós digitamos:

SELECT AVG(Height)
FROM People
WHERE Gender = ‘Male’

Mas e se introduzirmos outros gêneros em nossa mesa? Nesse caso, teríamos que escrever consultas adicionais e coletar manualmente os dados que precisamos. É mais fácil GRUPAR nossos dados POR Gender e depois calcular a altura média para cada grupo, como mostrado abaixo.

SELECT Gender, AVG(Height)
FROM People
GROUP BY Gender

+--------+-------------+
| Gender | AVG(Height) |
+--------+-------------+
| Female |    168.6667 |
| Male   |    179.5000 |
+--------+-------------+

O agrupamento parece fácil, certo? Tudo o que você precisa fazer é adicionar a cláusula GROUP BY à sua instrução SQL. Suponha, porém, que precisamos ter dois parâmetros em nossa busca. Nesse caso, precisaríamos agrupar por duas colunas. Digamos que queremos saber quantos homens e mulheres têm olhos azuis, marrons ou verdes. Nós digitaríamos:

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color

+--------+-----------+----------+
| Gender | Eye_color | COUNT(*) |
+--------+-----------+----------+
| Female | Blue      |        1 |
| Female | Brown     |        1 |
| Female | Green     |        1 |
| Male   | Blue      |        1 |
| Male   | Brown     |        1 |
+--------+-----------+----------+

Isto é apenas uma introdução a GROUP BY. Você pode usá-la de muitas maneiras. Por que não tentar experimentar com diferentes funções agregadas (como AVG e COUNT) para ter uma melhor compreensão de GROUP BY?

Erros comuns do GROUP BY

Embora o GROUP BY pareça bastante fácil de usar, é comum que os novatos em SQL se vejam confrontados por algumas mensagens de erro confusas. Aqui estão algumas que encontramos com bastante frequência:

1. Seleção de múltiplos valores

O mais importante a ser lembrado ao utilizar GROUP BY é que o que quer que você vá SELECIONAR deve ser um valor único. É por isso que precisamos usar uma função agregada: é preciso ter vários valores e produzir um único valor para retornar.

Para torná-lo um pouco mais claro, vamos dar uma olhada em nossos grupos:

+--------+--------+
| Gender | Height |
+--------+--------+
| Male   |    180 |
|        |    179 |
| Female |    172 |
|        |    164 |
|        |    170 |
+--------+--------+

Quando perguntamos por Altura agrupada por Gender, queremos obter um único valor. Mas aqui Male tem dois valores de altura e Female tem três. Qual devemos escolher?

SELECT Gender, Height
FROM People
GROUP BY Gender;

(MYSQL) ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.People.Height' which is not functionally dependent on columns 
in GROUP BY clause;

É por isso que precisamos usar uma função agregada (como o AVG, que usamos anteriormente) para obter um único valor.

2. Usando WHERE para filtrar resultados

Suponhamos que só queremos ver resultados de cor dos olhos para grupos de pessoas com mais de 170 cm de altura. Se tentarmos colocar isso na cláusula WHERE, como mostrado abaixo:

SELECT Gender, Eye_color, COUNT(*)
FROM People
WHERE AVG(Height) > 170
GROUP BY Gender, Eye_color

Recebemos a mensagem de erro mostrada abaixo:

(MYSQL) ERROR 1111 (HY000): Invalid use of group function

Isso porque o banco de dados está agrupando nossos registros após filtrá-los. Queremos que eles filtrem o resultado da declaração GROUP BY. Para fazer isso, usamos outra cláusula chamada HAVING. Ela vem depois de GROUP BY e funciona como WHERE. A diferença é que você pode usar funções agregadas nela. Para obter o resultado desejado, nós escreveríamos este código:

SELECT Gender, Eye_color, COUNT(*)
FROM People
GROUP BY Gender, Eye_color
HAVING AVG(Height) > 170

Lembre-se de que no banco de dados, uma instrução SQL SELECT sempre é executada nesta ordem:

1. DESDE
2. ONDE
3. GRUPO POR
4. HAVING
5. SELECIONE
6. ORDEM POR

Pegamos uma fonte de dados, filtramos os registros, agrupamo-los, filtramos os grupos, selecionamos as colunas desejadas e depois as classificamos.

3. Erros de Entidades Múltiplas

Isto é um pouco mais avançado. Quando juntarmos várias mesas, é bem possível que algumas entidades tenham os mesmos nomes. Portanto, muitas vezes queremos resultados agrupados por ID da entidade em vez do nome da entidade.

Por exemplo, suponha que você esteja olhando para os dados da cidade de Varsóvia. Há uma Varsóvia que é a capital da Polônia e uma Varsóvia que está no estado de Indiana, EUA.

Portanto, digamos que temos uma tabela definida assim:

Table City:
---------------------
| ID | Name | Award | 
---------------------

Esta tabela descreve as cidades que receberam um ou mais prêmios. Uma cidade é identificada por sua identidade e pode receber muitos prêmios.

Se quiséssemos ver o número de prêmios recebidos por uma cidade e ter esta informação agrupada pelo nome da cidade, talvez utilizássemos esta consulta:

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.Name

O problema é que, usando City.Name, os prêmios para Varsóvia, Polônia e Varsóvia, Indiana seriam adicionados juntos. Afinal de contas, ambos são Varsóvia! No entanto, são lugares diferentes e, como tal, têm valores City.ID diferentes. Quando se pensa em uma cidade como uma entidade de banco de dados, ela é identificada por seu ID e não por seus atributos (como Name). Se agruparmos os resultados por ID, obteremos as informações corretas. Como ainda queremos exibir o nome, usaremos algo como isto:

SELECT City.Name, COUNT(Award)
FROM City
GROUP BY City.ID

O resultado terá entradas separadas para as diferentes Varsóvias com os valores que desejamos. (Normalmente, haveria outra coluna, como "país" ou "estado", para diferenciar entre essas duas cidades. Mas para dar o exemplo, digamos que não existe).

4. Usando valores que são "Não Agregados".

No exemplo acima, selecionamos o atributo City.Name e agrupamos os resultados através do atributo City.ID. Em nossa tabela, todos os registros com o mesmo ID também têm o mesmo nome de cidade. Alguns bancos de dados não terão problemas com isso e retornarão os resultados esperados, mas outros lhe darão um erro dizendo que City.Name não está na cláusula GROUP BY e que não está agregado. Reproduzi a mensagem de erro do MySQL abaixo:

(MYSQL) ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains 
nonaggregated column 'vertabelo.City.Name' which is not functionally dependent on columns 
in GROUP BY clause;

Mas é um valor único! Como corrigimos este erro? Bem, já que um City.ID significa exatamente um City.Name, podemos colocar ambos na cláusula GROUP BY:

SELECT City.Name, COUNT(*)
FROM City
JOIN Person ON (Person.CityID = City.ID)
GROUP BY City.ID, City.Name

Isto deve corrigir o problema.

A prática torna perfeito

Se você ainda não sabe como ou quando usar a cláusula GROUP BY ou se quiser praticar o uso da mesma, verifique nosso curso SQL Queries. Ele cobre todas as coisas básicas, bem como as mais avançadas, assim você terá muitas oportunidades para aperfeiçoar suas habilidades em SQL.