Resumir dados com GROUP BY

Embora as funções de agregação sejam úteis para análise, convém organizar seus dados em subconjuntos antes de resumi-los. Nesta seção, você aprenderá a 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 agora está disponível para processamento adicional. Você pode usar a cláusula GROUP BY para subdividir o conteúdo dessa 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 examinar 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 todos os elementos das linhas de origem que estão na lista SELECT, mas que não aparecem na lista GROUP BY. Esse requisito terá um impacto na forma como você escreve suas cláusulas SELECT e HAVING.

Então, qual é a diferença entre escrever a consulta com um GROUP BY ou um DISTINCT? Se tudo o que você deseja saber são os valores distintos para CustomerID, não há diferença. Porém, com GROUP BY, podemos adicionar outros elementos à lista SELECT que são agregados para cada grupo.

A função de agregação mais simples é COUNT(*). A consulta a seguir usa as 830 linhas de origem originais do CustomerID e as agrupa em 89 grupos, com base nos valores 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 mostra quantas linhas na tabela SalesOrderHeader pertencem a cada cliente.

Observe que 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, deverá 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:

As cláusulas em uma instrução SELECT são aplicadas na seguinte ordem:

1. FROM
2. WHERE
3. AGRUPAR POR
4. TER
5. SELECT
6. ORDENAR POR

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 referenciar 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 terá êxito, agrupando e classificando os resultados pela 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 instruções GROUP BY em SELECT é entender por que o seguinte tipo de mensagem de erro ocorre:

Msg 8120, Nível 16, Estado 1, Linha 2 Coluna <column_name> é inválido na lista de seleção porque não está contido em uma função de agregação 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 de agregação que opera 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 de agregação.

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Essa 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 CustomerID . Mas as linhas para o mesmo CustomerID podem ter valores Diferentes PurchaseOrderNumber , portanto, qual dos valores é o que deve ser retornado?

Se você quiser ver pedidos por ID do cliente e por ordem de compra, poderá 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 pedidos de compra, juntamente com a contagem de pedidos para essa combinação.