Resumir dados com GROUP BY
Embora as funções agregadas sejam úteis para análise, você pode querer organizar seus dados em subconjuntos antes de resumi-los. Nesta seção, você aprenderá como fazer isso usando a cláusula GROUP BY.
Usando a cláusula GROUP BY
Como você aprendeu, quando sua instrução SELECT é processada, após a cláusula FROM e a cláusula WHERE terem sido avaliadas, uma tabela virtual é criada. O conteúdo da tabela virtual está agora disponível para processamento posterior. Você pode usar a cláusula GROUP BY para subdividir o conteúdo desta tabela virtual em grupos de linhas.
Para agrupar linhas, especifique um ou mais elementos na cláusula GROUP BY:
GROUP BY <value1> [, <value2>, …]
GROUP BY cria grupos e coloca linhas em cada grupo, conforme determinado pelos elementos especificados na cláusula.
Por exemplo, a consulta a seguir resultará em um conjunto de linhas agrupadas, uma linha por CustomerID na tabela Sales.SalesOrderHeader . Outra maneira de ver o processo GROUP BY é que todas as linhas com o mesmo valor para CustomerID serão agrupadas e retornadas em uma única linha de resultado.
SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
A consulta acima é equivalente à seguinte consulta:
SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader
Depois que a cláusula GROUP BY tiver sido processada e cada linha tiver sido associada a um grupo, as fases posteriores da consulta deverão agregar quaisquer elementos das linhas de origem que estejam na lista SELECT, mas que não apareçam na lista GROUP BY. Este requisito terá um impacto na forma como escreve as suas cláusulas SELECT e HAVER.
Então, qual é a diferença entre escrever a consulta com um GRUPO POR ou um DISTINTO? Se tudo o que você quer saber são os valores distintos para CustomerID, não há diferença. Mas com GROUP BY, podemos adicionar outros elementos à lista SELECT que são então agregados para cada grupo.
A função agregada mais simples é COUNT(*). A consulta a seguir pega as 830 linhas de origem originais de CustomerID e as agrupa em 89 grupos, com base nos valores de CustomerID. Cada valor CustomerID distinto gera uma linha de saída na consulta GROUP BY
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Para cada valor CustomerID, a consulta agrega e conta as linhas, portanto, o resultado nos mostra quantas linhas na tabela SalesOrderHeader pertencem a cada cliente.
IDDeCliente
OrderCount
1234
3
1005
5
Note que o GROUP BY não garante a ordem dos resultados. Muitas vezes, como resultado da maneira como a operação de agrupamento é executada pelo processador de consulta, os resultados são retornados na ordem dos valores do grupo. No entanto, você não deve confiar nesse comportamento. Se você precisar que os resultados sejam classificados, você deve incluir explicitamente uma cláusula ORDER:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;
Desta vez, os resultados são retornados na ordem especificada:
IDDeCliente
OrderCount
1005
5
1234
3
As cláusulas em uma instrução SELECT são aplicadas na seguinte ordem:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECIONAR
- ORDER BY
Os aliases de coluna são atribuídos na cláusula SELECT, que ocorre após a cláusula GROUP BY, mas antes da cláusula ORDER BY. Você pode fazer referência a um alias de coluna na cláusula ORDER BY, mas não na cláusula GROUP BY. A consulta a seguir resultará em um erro de nome de coluna inválido:
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;
No entanto, a consulta a seguir será bem-sucedida, agrupando e classificando os resultados pelo ID do cliente.
SELECT CustomerID AS Customer,
COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;
Solução de problemas de erros GROUP BY
Um obstáculo comum para se sentir confortável com o uso de GROUP BY em instruções SELECT é entender por que o seguinte tipo de mensagem de erro ocorre:
A Mensagem 8120, Nível 16, Estado 1, Linha 2 Coluna <column_name> é inválida na lista de seleção porque não está contida em uma função agregada ou na cláusula GROUP BY.
Por exemplo, a consulta a seguir é permitida porque cada coluna na lista SELECT é uma coluna na cláusula GROUP BY ou uma função agregada operando em cada grupo:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
A consulta a seguir retornará um erro porque PurchaseOrderNumber não faz parte do GROUP BY e não é usado com uma função agregada.
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
Esta consulta retorna o erro:
Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Aqui está outra maneira de pensar sobre isso. Essa consulta retorna uma linha para cada valor de CustomerID . Mas as linhas para o mesmo CustomerID podem ter valores PurchaseOrderNumber diferentes, então qual dos valores é o que deve ser retornado?
Se quiser ver pedidos por ID de cliente e por ordem de compra, você pode adicionar a coluna PurchaseOrderNumber à cláusula GROUP BY, da seguinte maneira:
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;
Essa consulta retornará uma linha para cada cliente e cada combinação de ordem de compra, juntamente com a contagem de pedidos para essa combinação.