Resumir dados com GROUP BY

  • 3 minutos

Embora as funções de agregação sejam úteis para análise, pode ser bom 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 a 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 as 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 observar 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 à consulta a seguir:

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 estão na lista SELECT, mas que não aparecem na lista GROUP BY. Esse requisito terá um impacto sobre 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. Mas, com GROUP BY, podemos adicionar outros elementos à lista SELECT, que são entã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 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, os resultados nos mostram quantas linhas na tabela SalesOrderHeader pertencem a cada cliente.

CustomerID

OrderCount

1234

3

1005

1

Observe que GROUP BY não garante a ordem dos resultados. Frequentemente, como resultado da maneira como a operação de agrupamento é executada pelo processador de consultas, os resultados são retornados na ordem dos valores do grupo. No entanto, você não deve contar com isso. Se precisar que os resultados sejam classificados, inclua 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:

CustomerID

OrderCount

1005

1

1234

3

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

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

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 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;

Solucionando problemas com erros de GROUP BY

Um obstáculo comum para familiarizar-se com o uso de GROUP BY em instruções SELECT é entender por que ocorre o tipo de mensagem de erro a seguir:

Mensagem 8120, Nível 16, Estado 1, Linha 2 A coluna <nome_da_coluna> é inválida na lista de seleção, porque não está contida em uma função de agregação nem 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 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 é usada 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 isso. Essa consulta retorna uma linha para cada valor de customerID. Mas as linhas para o mesmo CustomerID podem ter valores de purchaseOrderNumber diferentes, portanto, qual dos valores 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 forma:

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.