CLÁUSULA SELECT - GROUP BY (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Ponto de extremidade de análise de SQL no Microsoft FabricDepósito no Microsoft FabricBanco de dados SQL no Microsoft Fabric

Uma SELECT cláusula de instrução que divide o resultado da consulta em grupos de linhas, geralmente executando uma ou mais agregações em cada grupo. A SELECT instrução retorna uma linha para cada grupo.

Syntax

Convenções de sintaxe de Transact-SQL

Sintaxe compatível com ISO para SQL Server e Banco de Dados SQL do Azure:

GROUP BY {
      column-expression
    | ROLLUP ( <group_by_expression> [ , ...n ] )
    | CUBE ( <group_by_expression> [ , ...n ] )
    | GROUPING SETS ( <grouping_set> [ , ...n ]  )
    | () --calculates the grand total
} [ , ...n ]

<group_by_expression> ::=
      column-expression
    | ( column-expression [ , ...n ] )

<grouping_set> ::=
      () --calculates the grand total
    | <grouping_set_item>
    | ( <grouping_set_item> [ , ...n ] )

<grouping_set_item> ::=
      <group_by_expression>
    | ROLLUP ( <group_by_expression> [ , ...n ] )
    | CUBE ( <group_by_expression> [ , ...n ] )

Sintaxe não compatível com ISO para SQL Server e Banco de Dados SQL do Azure (somente compatibilidade com versões anteriores):

GROUP BY {
       ALL column-expression [ , ...n ]
    | column-expression [ , ...n ]  WITH { CUBE | ROLLUP }
       }

Sintaxe para Azure Synapse Analytics:

GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]
    | column-expression
    | ROLLUP ( <group_by_expression> [ , ...n ] )
} [ , ...n ]

Sintaxe para PDW (Analytics Platform System):

GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]
    | column-expression
} [ , ...n ]

Arguments

column-expression

Especifica uma coluna ou um cálculo não de entrada em uma coluna. Essa coluna pode pertencer a uma tabela, a uma tabela derivada ou a uma exibição. A coluna deve aparecer na FROM cláusula da SELECT instrução, mas não precisa aparecer na SELECT lista.

Para saber quais são as expressões válidas, confira Expressão.

A coluna deve aparecer na FROM cláusula da SELECT instrução, mas não é necessária para aparecer na SELECT lista. No entanto, você deve incluir cada tabela ou coluna de exibição na GROUP BY lista se usá-la em qualquer expressão não de entrada na <select> lista.

Opções GROUP BY

As opções a seguir estendem a cláusula básica GROUP BY para dar suporte à agregação hierárquica, resumo multidimensional, combinações de agrupamento personalizado e comportamentos de execução específicos da plataforma. As consultas podem usar essas opções para produzir subtotais e totais gerais em uma única operação lógica.

• ROLLUP ( <group_by_expression> [ , ... n ] )

  Gera subtotais hierárquicos para as colunas listadas e um total geral final (por exemplo, (a,b,c), (a,b), , (a), ()). Use-o para relatórios de detalhamento, comoo trimestre do>ano>.

• CUBE ( <group_by_expression> [ , ... n ] )

  Produz todas as combinações das colunas especificadas (o total de 2^n reticências) mais o total geral. Use-o para análise multidimensional em cada fatia.

• CONJUNTOS DE AGRUPAMENTO ( <grouping_set> [ , ... n ] )

  Define os agrupamentos exatos para computação (incluindo () para o total geral) em uma passagem. Essa opção é funcionalmente semelhante a uma UNION ALL das várias GROUP BY consultas, mas otimizada em conjunto.

• () (conjunto de agrupamento vazio)

  Abreviação para calcular apenas o total geral em todas as linhas. Use-o sozinho como GROUP BY () ou dentro GROUPING SETS.

• ALL column-expression [ , ... n ](não ISO; compatibilidade com versões anteriores)

  Abreviação para agrupar por todos os itens selecionados não agregados. Retido para compatibilidade; disponibilidade e semântica variam.

• column-expression [ , ... n ] WITH { CUBE | 'ROLLUP }(formulário herdado)

  Sintaxe não ISO mais antiga que seja equivalente a GROUP BY CUBE(...) ou GROUP BY ROLLUP(...). Com suporte somente para compatibilidade com versões anteriores. Use as subclausas ISO quando possível.

• WITH (DISTRIBUTED_AGG)

  Sugere a execução distribuída para agregações ao agrupar por uma única coluna. Os pools de SQL dedicados do Azure Synapse Analytics e o PDW (Analytics Platform System) são as únicas plataformas que dão suporte a essa opção.

Expressão de coluna GROUP BY [ ,... n ]

Agrupa os resultados da SELECT instrução de acordo com os valores em uma lista de uma ou mais expressões de coluna.

Por exemplo, essa consulta cria uma Sales tabela com colunas para Region, Territorye Sales. Ele insere quatro linhas e duas das linhas têm valores correspondentes para Region e Territory.

CREATE TABLE Sales
(
    Region VARCHAR (50),
    Territory VARCHAR (50),
    Sales INT
);
GO

INSERT INTO Sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO Sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO Sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO Sales VALUES (N'United States', N'Montana', 100);

A Sales tabela contém estas linhas:

Esta próxima consulta agrupa Region e Territory retorna a soma agregada para cada combinação de valores.

SELECT Region,
       Territory,
       SUM(sales) AS TotalSales
FROM Sales
GROUP BY Region, Territory;

O resultado da consulta tem três linhas, pois há três combinações de valores para Region e Territory. O TotalSales para o Canadá e a Colúmbia Britânica é a soma de duas linhas.

A expressão de coluna em GROUP BY não pode conter:

• Um alias de coluna que você define na SELECT lista. Ele pode usar um alias de coluna para uma tabela derivada definida na FROM cláusula.
• Uma coluna do tipo text, ntext ou image. No entanto, você pode usar uma coluna de texto, ntext ou imagem como um argumento para uma função que retorna um valor de um tipo de dados válido. Por exemplo, a expressão pode usar SUBSTRING() e CAST(). Essa regra também se aplica a expressões na HAVING cláusula.
• Métodos de tipo de dados xml. Ele pode incluir uma função definida pelo usuário que usa métodos de tipo de dados xml . Ele pode incluir uma coluna computada que usa métodos de tipo de dados xml .
• Uma subconsulta. A consulta retorna o erro 144.
• Uma coluna de uma exibição indexada.

As seguintes instruções são permitidas:

SELECT ColumnA,
       ColumnB
FROM T
GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB
FROM T
GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB
FROM T
GROUP BY ColumnA + ColumnB;

SELECT ColumnA + ColumnB + constant
FROM T
GROUP BY ColumnA, ColumnB;

As instruções a seguir não são permitidas:

SELECT ColumnA,
       ColumnB
FROM T
GROUP BY ColumnA + ColumnB;

SELECT ColumnA + constant + ColumnB
FROM T
GROUP BY ColumnA + ColumnB;

GROUP BY ROLLUP ()

Cria um grupo para cada combinação de expressões de coluna. Além disso, ele acumula os resultados em subtotais e totais gerais. Quando cria os grupos, ele se move da direita para a esquerda, diminuindo o número de expressões de coluna para agrupamento e agregações.

A ordem da coluna afeta a ROLLUP saída e pode afetar o número de linhas no conjunto de resultados.

Por exemplo, GROUP BY ROLLUP (col1, col2, col3, col4) cria grupos para cada combinação de expressões de coluna nas seguintes listas:

• Col1, Col2, Col3, Col4
• col1, col2, col3, NULL
• col1, col2, NULL, NULL
• COL1, ZERO, NULL, NULL
• NULL, NULL, NULL, NULL (o grupo com os NULL valores é o total geral)

Usando a tabela do exemplo anterior, esse código executa uma GROUP BY ROLLUP operação em vez de uma básica GROUP BY.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Region, Territory);

O resultado da consulta tem as mesmas agregações que o básico GROUP BY sem o ROLLUP. Além disso, ele cria subtotais para cada valor de Região. Por fim, ele fornece um total geral para todas as linhas. O resultado será semelhante a este:

GROUP BY CUBE ()

GROUP BY CUBE cria grupos para todas as combinações possíveis de colunas. Para GROUP BY CUBE (a, b), os resultados têm grupos para valores exclusivos de (a, b), (NULL, b)e (a, NULL)(NULL, NULL).

Usando a tabela dos exemplos anteriores, esse código executa uma GROUP BY CUBE operação em Região e Território.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE(Region, Territory);

O resultado da consulta tem grupos para valores exclusivos de (Region, Territory), (NULL, Territory)e (Region, NULL)(NULL, NULL). Os resultados serão semelhantes a estes:

AGRUPAR POR CONJUNTOS DE AGRUPAMENTO ()

A GROUPING SETS opção combina várias GROUP BY cláusulas em uma GROUP BY cláusula. Os resultados são os mesmos usados UNION ALL nos grupos especificados.

Por exemplo, GROUP BY ROLLUP (Region, Territory) e GROUP BY GROUPING SETS ( ROLLUP (Region, Territory)) retornam os mesmos resultados.

Quando GROUPING SETS tem dois ou mais elementos, os resultados são uma união dos elementos. Este exemplo retorna a união do e ROLLUP dos CUBE resultados para Região e Território.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS(ROLLUP(Region, Territory), CUBE(Region, Territory));

Os resultados são os mesmos dessa consulta que retorna uma união das duas GROUP BY instruções.

SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Region, Territory)
UNION ALL
SELECT Region,
       Territory,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE(Region, Territory);

O SQL não consolida grupos duplicados gerados para uma GROUPING SETS lista. Por exemplo, em GROUP BY ((), CUBE (Region, Territory)), ambos os elementos retornam uma linha para o total geral e ambas as linhas aparecem nos resultados.

Suporte para recursos ISO e ANSI SQL-2006 GROUP BY

A GROUP BY cláusula dá suporte a todos os GROUP BY recursos incluídos no padrão SQL-2006 com as seguintes exceções de sintaxe:

• Conjuntos de agrupamento não são permitidos na cláusula, a GROUP BY menos que façam parte de uma lista explícita GROUPING SETS . Por exemplo, é permitido no padrão, GROUP BY Column1, (Column2, ...ColumnN) mas não no Transact-SQL. O Transact-SQL é compatível com GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) e GROUP BY Column1, Column2, ... ColumnN, que são semanticamente equivalentes. Essas cláusulas são semanticamente equivalentes ao exemplo anterior GROUP BY . Essa restrição evita a possibilidade de serem GROUP BY Column1, (Column2, ...ColumnN) mal interpretadas como GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), que não são semanticamente equivalentes.

• Conjuntos de agrupamentos não são permitidos em conjuntos de agrupamentos. Por exemplo, GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) é permitido no padrão SQL-2006, mas não no Transact-SQL. Transact-SQL permite ouGROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn), que são semanticamente equivalentes GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn)) ao primeiro GROUP BY exemplo e têm uma sintaxe mais clara.

GRUPO POR ()

Especifica o grupo vazio que gera o total geral. Esse grupo é útil como um dos elementos de um GROUPING SET. Por exemplo, essa instrução fornece o total de vendas para cada região e, em seguida, fornece o total geral para todas as regiões.

SELECT Region,
       SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS(Region, ());

GROUP BY ALL column-expression [ ,... n ]

Aplica-se a: SQL Server e Azure SQL Database

Especifica se todos os grupos devem ser incluídos nos resultados, independentemente de atenderem aos critérios de pesquisa na WHERE cláusula. Grupos que não atendem aos critérios de pesquisa têm NULL para a agregação.

GROUP BY ALL:

• Não há suporte em consultas que acessam tabelas remotas se também houver uma WHERE cláusula na consulta.
• Falha em colunas que têm o atributo FILESTREAM.

Suporte para recursos ISO e ANSI SQL-2006 GROUP BY

A GROUP BY cláusula dá suporte a todos os GROUP BY recursos incluídos no padrão SQL-2006 com as seguintes exceções de sintaxe:

• Você só pode usar GROUP BY ALL e GROUP BY DISTINCT em uma cláusula básica GROUP BY que contém expressões de coluna. Você não pode usá-los com as GROUPING SETSconstruções , ROLLUP, CUBEWITH CUBEou WITH ROLLUP . ALL é o padrão e está implícito. Você só pode usá-lo na sintaxe compatível com versões anteriores.

GROUP BY column-expression [ ,... n ] WITH { CUBE | ROLLUP }

Aplica-se a: SQL Server e Azure SQL Database

COM (DISTRIBUTED_AGG)

Aplica-se a: PDW (Azure Synapse Analytics and Analytics Platform System)

A DISTRIBUTED_AGG dica de consulta força o sistema MPP (processamento paralelo maciço) a redistribuir uma tabela em uma coluna específica antes de executar uma agregação. Você pode usar a DISTRIBUTED_AGG dica de consulta em apenas uma coluna na GROUP BY cláusula. Depois que a consulta for concluída, a tabela redistribuída será descartada. A tabela original não foi alterada.

Observações

Como GROUP BY interage com a instrução SELECT

SELECT Lista:

• Agregados vetoriais. Se você incluir funções de agregação na SELECT lista, GROUP BY calculará um valor de resumo para cada grupo. Essas funções são conhecidas como agregações de vetor.
• Agregados distintos. As agregações AVG(DISTINCT <column_name>), COUNT(DISTINCT <column_name>)e SUM(DISTINCT <column_name>) trabalhar com ROLLUP, CUBEe GROUPING SETS.

Cláusula WHERE:

• O SQL remove linhas que não atendem às condições na WHERE cláusula antes de executar qualquer operação de agrupamento.

Cláusula HAVING:

• O SQL usa a HAVING cláusula para filtrar grupos no conjunto de resultados.

Cláusula ORDER BY:

• Use a ORDER BY cláusula para ordenar o conjunto de resultados. A GROUP BY cláusula não ordena o conjunto de resultados.

NULL valores:

• Se uma coluna de agrupamento contiver NULL valores, o Mecanismo de Banco de Dados tratará todos os NULL valores como iguais e os coletará em um único grupo.

Limitações

Aplica-se a: SQL Server e Azure Synapse Analytics

Para uma GROUP BY cláusula que usa ROLLUP, CUBEou GROUPING SETS, o número máximo de expressões é 32. O número máximo de grupos é 4.096 (2¹²). Os exemplos a seguir falham porque a GROUP BY cláusula tem mais de 4.096 grupos.

• O exemplo a seguir gera 4.097 (2¹² + 1) conjuntos de agrupamento e, em seguida, falha.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b)
  

• O exemplo a seguir gera 4.097 (2¹² + 1) grupos e, em seguida, falha. CUBE () e o conjunto de agrupamentos () produzem uma linha de total geral e os conjuntos de agrupamentos duplicados não são eliminados.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
  

• Este exemplo usa a sintaxe compatível com versões anteriores. Ele gera 8.192 (2¹³) conjuntos de agrupamento e, em seguida, falha.

  GROUP BY CUBE (a1, ..., a13)
  GROUP BY a1, ..., a13 WITH CUBE
  

  Para cláusulas compatíveis com versões anteriores GROUP BY que não contêm CUBE ou ROLLUP, os GROUP BY tamanhos das colunas, as colunas agregadas e os valores agregados envolvidos na consulta limitam o número de GROUP BY itens. Esse limite se origina do limite de 8.060 bytes na tabela de trabalho intermediária que contém resultados intermediários da consulta. Você pode usar um máximo de 12 expressões de agrupamento quando especificar CUBE ou ROLLUP.

Comparação de recursos GROUP BY com suporte

A tabela a seguir descreve os GROUP BY recursos que diferentes produtos dão suporte.

¹Nível de compatibilidade de banco de dados 100 e superior.

² A mensagem de erro retornada é: Incorrect syntax near the keyword 'cube'|'rollup'.

Examples

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que você pode baixar na página inicial Microsoft SQL Server Samples and Community Projects.

A. Usar uma cláusula GROUP BY básica

O exemplo a seguir recupera o total para cada tabela SalesOrderID da tabela SalesOrderDetail. Este exemplo usa o AdventureWorks.

SELECT SalesOrderID,
       SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

B. Usar uma cláusula GROUP BY com várias tabelas

O exemplo a seguir recupera o número de funcionários de cada City da tabela Address unida à tabela EmployeeAddress. Este exemplo usa o AdventureWorks.

SELECT a.City,
       COUNT(bea.AddressID) AS EmployeeCount
FROM Person.BusinessEntityAddress AS bea
     INNER JOIN Person.Address AS a
         ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;

C. Usar uma cláusula GROUP BY com uma expressão

O exemplo a seguir recupera as vendas totais durante cada ano usando a função DATEPART. Você deve incluir a mesma expressão na SELECT lista e GROUP BY na cláusula.

SELECT DATEPART(yyyy, OrderDate) AS N'Year',
       SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy, OrderDate)
ORDER BY DATEPART(yyyy, OrderDate);

D. Usar uma cláusula GROUP BY com uma cláusula HAVING

O exemplo a seguir usa a HAVING cláusula para especificar quais grupos gerados na GROUP BY cláusula devem ser incluídos no conjunto de resultados.

SELECT DATEPART(yyyy, OrderDate) AS N'Year',
       SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy, OrderDate)
HAVING DATEPART(yyyy, OrderDate) >= N'2003'
ORDER BY DATEPART(yyyy, OrderDate);

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

E. Uso básico da cláusula GROUP BY

O exemplo a seguir localiza a quantidade total de todas as vendas em cada dia. A consulta retorna uma linha que contém a soma de todas as vendas para cada dia.

-- Uses AdventureWorksDW
SELECT OrderDateKey,
       SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
ORDER BY OrderDateKey;

F. Uso básico da dica de DISTRIBUTED_AGG

Este exemplo usa a dica de consulta DISTRIBUTED_AGG para forçar o dispositivo a embaralhar a tabela na CustomerKey coluna antes de executar a agregação.

-- Uses AdventureWorksDW
SELECT CustomerKey,
       SUM(SalesAmount) AS sas
FROM FactInternetSales
GROUP BY CustomerKey WITH(DISTRIBUTED_AGG)
ORDER BY CustomerKey DESC;

G. Variações de sintaxe para GROUP BY

Quando a lista de seleção não tiver agregações, você deverá incluir cada coluna na lista de seleção na GROUP BY lista. Você pode incluir colunas computadas na lista de seleção, mas não é necessário incluí-las na GROUP BY lista. Estes exemplos mostram instruções sintaticamente válidas SELECT :

-- Uses AdventureWorks
SELECT LastName,
       FirstName
FROM DimCustomer
GROUP BY LastName, FirstName;

SELECT NumberCarsOwned
FROM DimCustomer
GROUP BY YearlyIncome, NumberCarsOwned;

SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost
FROM FactInternetSales
GROUP BY SalesAmount, TaxAmt, Freight;

SELECT SalesAmount,
       SalesAmount * 1.10 AS SalesTax
FROM FactInternetSales
GROUP BY SalesAmount;

SELECT SalesAmount
FROM FactInternetSales
GROUP BY SalesAmount, SalesAmount * 1.10;

H. Usar uma cláusula GROUP BY com várias expressões GROUP BY

O exemplo a seguir agrupa os resultados usando vários critérios de GROUP BY. Se, em cada OrderDateKey grupo, existirem subgrupos que o DueDateKey valor diferencia, a consulta definirá um novo agrupamento para o conjunto de resultados.

-- Uses AdventureWorks
SELECT OrderDateKey,
       DueDateKey,
       SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey
ORDER BY OrderDateKey;

I. Usar uma cláusula GROUP BY com uma cláusula HAVING

O exemplo a seguir usa a HAVING cláusula para especificar os grupos gerados na GROUP BY cláusula que deve ser incluído no conjunto de resultados. Somente os grupos com datas de pedido em 2004 ou posteriores são incluídos nos resultados.

-- Uses AdventureWorks
SELECT OrderDateKey,
       SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING OrderDateKey > 20040000
ORDER BY OrderDateKey;

Conteúdo relacionado

• GROUPING_ID (Transact-SQL)
• AGRUPAMENTO (Transact-SQL)
• SELECT (Transact-SQL)
• Cláusula SELECT (Transact-SQL)