GROUP BY (Transact-SQL)

Agrupa um conjunto de linhas selecionadas em um conjunto de linhas de resumo pelos valores de uma ou mais colunas ou expressões no SQL Server 2008 R2. Uma linha é retornada para cada grupo. Funções de agregação na lista de <seleção> da cláusula SELECT fornecem informações sobre cada grupo em vez de linhas individuais.

A cláusula GROUP BY tem uma sintaxe de conformidade ISO e uma sintaxe de não conformidade ISO. Só um estilo de sintaxe pode ser usado em uma instrução SELECT única. Use a sintaxe de conformidade ISO para todo o novo trabalho. A sintaxe de não conformidade ISO é fornecida para compatibilidade com versões anteriores.

Neste tópico, uma cláusula GROUP BY pode ser descrita como geral ou simples:

• Uma cláusula GROUP BY geral inclui GROUPING SETS, CUBE, ROLLUP, WITH CUBE ou WITH ROLLUP.

• Uma cláusula GROUP BY simples não inclui GROUPING SETS, CUBE, ROLLUP, WITH CUBE nem WITH ROLLUP. GROUP BY (), total geral, é considerado um GROUP BY simples.

convenções de sintaxe Transact-SQL (Transact-SQL)

Sintaxe

        ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=
    <group by item> [ ,...n ]

<group by item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>
    | <grouping sets spec>
    | <grand total>

<simple group by item> ::=
    <column_expression>

<rollup spec> ::=
    ROLLUP ( <composite element list> )<cube spec> ::=
    CUBE ( <composite element list> )<composite element list> ::=
    <composite element> [ ,...n ]

<composite element> ::=
    <simple group by item>
    | ( <simple group by item list> )<simple group by item list> ::=
    <simple group by item> [ ,...n ]

<grouping sets spec> ::=
    GROUPING SETS ( <grouping set list> )<grouping set list> ::=
    <grouping set> [ ,...n ]

<grouping set> ::=
    <grand total>
    | <grouping set item>
    | ( <grouping set item list> )<empty group> ::=()<grouping set item> ::=
    <simple group by item>
    | <rollup spec>
    | <cube spec>

<grouping set item list> ::=
    <grouping set item> [ ,...n ]

        Non-ISO-Compliant Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
    [ WITH { CUBE | ROLLUP } ] 
]

Argumentos

• <column_expression>
  É a expressão na qual a operação de agrupamento é executada.

• ROLLUP ( )
  Gera as linhas de agregação GROUP BY simples, mais subtotal ou linhas de superagregação e também uma linha de total geral.

  O número de agrupamentos retornado iguala o número de expressões na <composite element list> mais um. Por exemplo, considere a instrução a seguir.

  SELECT a, b, c, SUM ( <expression> )
  FROM T
  GROUP BY ROLLUP (a,b,c);
  

  Uma linha com um subtotal é gerada para cada combinação exclusiva de valores (a, b, c), (a, b) e (a). Uma linha total geral também é calculada.

  As colunas são acumuladas da direita para esquerda. A ordem da coluna afeta os agrupamentos de saída de ROLLUP e pode afetar o número de linhas no conjunto de resultados.

• CUBE ( )
  Gera linhas GROUP BY simples de agregação, linhas de superagregação de ROLLUP e linhas de tabulação cruzada.

  CUBE produz um agrupamento para todas as permutações de expressões na <lista de elementos composta>.

  O número de agrupamentos que é gerado é igual a (2n), onde n = ao número de expressões na <lista de elementos composta>. Por exemplo, considere a instrução a seguir.

  SELECT a, b, c, SUM (<expression>)
  FROM T
  GROUP BY CUBE (a,b,c);
  

  Uma linha é produzida para cada combinação exclusiva de valores (a, b, c), (a, b), (a, c), (b, c), (a), (b) e (c) com um subtotal para cada linha e uma linha de total geral.

  A ordem da coluna não afeta a saída de CUBE.

• GROUPING SETS ( )
  Especifica vários agrupamentos de dados em uma consulta. Somente os grupos especificados são agregados e não o conjunto completo de agregações gerado por CUBE ou ROLLUP. Os resultados são o equivalente de UNION ALL dos grupos especificados. GROUPING SETS podem conter um único elemento ou uma lista de elementos. GROUPING SETS podem especificar agrupamentos equivalentes aos retornados por ROLLUP ou CUBE. Para obter exemplos, consulte Equivalentes GROUPING SETS. A <lista de itens de conjuntos de agrupamentos> pode conter ROLLUP ou CUBE.

• ( )
  O grupo vazio gera um total.

Sintaxe de não conformidade ISO

• ALL
  Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. Inclui todos os grupos e conjuntos de resultados, mesmo aqueles que não têm linhas que atendem às critério de pesquisa especificadas na cláusula WHERE. Quando ALL é especificado, valores nulos são retornados para as colunas de resumo de grupos que não satisfazem as critério de pesquisa. Não é possível especificar ALL com os operadores CUBE ou ROLLUP.

  GROUP BY ALL não tem suporte em consultas que acessam tabelas remotas se houver também uma cláusula WHERE na consulta. GROUP BY ALL falhará em colunas que têm o atributo FILESTREAM.

• group_by_expression
  É uma expressão na qual o agrupamento é executado. group_by_expression é também conhecido como coluna de agrupamento. group_by expression pode ser uma coluna ou uma expressão de não agregação que referencia uma coluna retornada pela cláusula FROM. Um alias de coluna que está definido na lista SELECT não pode ser usado para especificar uma coluna de agrupamento.

  Observação
  Colunas do tipo text, ntext e image não podem ser usadas em group_by_expression.

  Para cláusulas GROUP BY que não contêm CUBE nem ROLLUP, o número de itens de group_by_expression é limitado pelos tamanhos da coluna GROUP BY, as colunas de agregação e os valores de agregação envolvidos na consulta. Esse limite tem origem no limite de 8.060 bytes na tabela de trabalho intermediária que é necessária para manter resultados de consulta intermediários. Um máximo de 12 expressões de agrupamento é permitido quando CUBE ou ROLLUP é especificado.

  Não podem ser especificados métodos de tipo de dados xml diretamente em group_by_expression. Em vez disso, consulte uma função definida pelo usuário que usa métodos de tipo de dados xml ou que faça referência a uma coluna computada que os utilize.

• WITH CUBE
  Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. especifica que em uma adição às linhas comuns fornecidas por GROUP BY, linhas de resumo são introduzidas no conjunto de resultados. Uma linha de resumo GROUP BY é retornada para cada combinação possível de grupo e subgrupo no conjunto de resultados. Use a função GROUPING para determinar se valores nulos no conjunto de resultados são valores resumidos GROUP BYs.

  O número de linhas de resumo em um conjunto de resultados é determinado pelo número de colunas incluídas na cláusula GROUP BY. Como CUBE retorna cada possível combinação de grupo e subgrupo, o número de linhas é o mesmo, independentemente da ordem na qual as colunas de agrupamento são especificadas.

• WITH ROLLUP
  Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. especifica que em uma adição às linhas comuns fornecidas por GROUP BY, linhas de resumo são introduzidas no conjunto de resultados. São resumidos grupos em uma ordem hierárquica, do nível mais baixo no grupo para o mais alto. A hierarquia de grupo é determinada pela ordem na qual as colunas de agrupamento são especificadas. A alteração da ordem das colunas de agrupamento pode afetar o número de linhas produzido no conjunto de resultados.

  Importante
  Agregações diferentes, por exemplo, AVG (DISTINCT column_name), COUNT (DISTINCT column_name) e SUM (DISTINCT column_name) não têm suporte quando se usa CUBE ou ROLLUP. Se forem usados, o Mecanismo de banco de dados do SQL Server retornará uma mensagem de erro e cancelará a consulta.

Comentários

Expressões na cláusula GROUP BY podem conter colunas de tabelas, exibições ou tabelas derivadas na cláusula FROM. Não é exigido que as colunas apareçam na lista de <seleção> da cláusula SELECT.

Cada coluna de tabela ou exibição em qualquer expressão de não agregação na lista de <seleção> deve estar incluída na lista GROUP BY:

• 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 seguintes instruções não são permitidas:

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

Se funções de agregação forem incluídas na <lista de seleção> da cláusula SELECET, GROUP BY calculará um valor resumido para cada grupo. São conhecidas como agregações de vetor.

Linhas que não atendem às condições na cláusula WHERE são removidas antes que qualquer operação de agrupamento seja executada.

A cláusula HAVING é usada com a cláusula GROUP BY para filtrar grupos no conjunto de resultados.

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

Se uma coluna de agrupamento contiver valores nulos, todos os valores nulos serão considerados iguais e colocados em um único grupo.

Não é possível usar GROUP BY com um alias para substituir um nome de coluna na cláusula AS, a menos que o alias substitua um nome de coluna em uma tabela derivada na cláusula FROM.

Conjuntos de agrupamentos duplicados em uma lista de GROUPING SETS não são eliminados. Conjuntos de agrupamentos duplicados podem ser gerados quando se especifica uma expressão de coluna mais de uma vez ou ao listar uma expressão de coluna também gerada por CUBE ou ROLLUP na lista de GROUPING SETS.

Agregações diferentes, por exemplo, AVG (DISTINCT column_name), COUNT (DISTINCT column_name), e SUM (DISTINCT column_name) têm suporte com ROLLUP, CUBE e GROUPING SETS.

ROLLUP, CUBE e GROUPING SETS não podem ser especificados em uma exibição indexada.

GROUP BY ou HAVING não podem ser usados diretamente em colunas de ntext, text ou image. Essas colunas podem ser usadas como argumentos em funções que retornam um valor de outro tipo de dados, como SUBSTRING() e CAST().

Não podem ser especificados métodos de tipo de dados xml diretamente em uma <column_expression>. Em vez disso, consulte uma função definida pelo usuário que usa métodos de tipo de dados xml ou faça referência a uma coluna computada que os utilize.

Limitações de GROUP BY para GROUPING SETS, ROLLUP e CUBE

Limitações de sintaxe

Não são permitidos GROUPING SETS na cláusula GROUP BY, a menos que sejam parte de uma lista de GROUPING SETS. Por exemplo, GROUP BY C1, (C2,..., Cn) não é permitido, mas GROUP BY GROUPING SETS (C1, (C2, ..., Cn)) é permitido.

GROUPING SETS não são permitidos em GROUPING SETS. Por exemplo, GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)) não é permitido.

As palavras-chave de não conformidade ISO ALL, WITH CUBE e WITH ROLLUP não são permitidas em uma cláusula GROUP BY com as palavras-chave ROLLUP, CUBE ou GROUPING SETS.

Limitações de tamanho

Para GROUP BY simples, não há nenhum limite no número de expressões.

Para uma cláusula GROUP BY que usa ROLLUP, CUBE ou GROUPING SETS, o número máximo de expressões é 32 e o número máximo de conjuntos de agrupamentos que pode ser gerado é 4096 (212). Os seguintes exemplos falham porque a cláusula GROUP BY é muito complexa:

• Os exemplos a seguir geram 8192 (213) conjuntos de agrupamentos.

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

• Os exemplos a seguir geram 4097 (212+1) conjuntos de agrupamentos.

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

• Os exemplos a seguir também geram 4097 (212+1) conjuntos de agrupamentos. 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), ())
  

Suporte para os recursos GROUP BY ISO e ANSI SQL-2006

No SQL Server 2008 e versões posteriores, a cláusula GROUP BY não pode conter uma subconsulta em uma expressão que é usada pela lista group by. O erro 144 é retornado.

O SQL Server 2008 e versões posteriores dão suporte a todos os recursos GROUP BY incluídos no SQL-2006 padrão com as seguintes exceções de sintaxe:

• Conjuntos de agrupamentos não são permitidos na cláusula GROUP BY, a menos que sejam parte de uma lista de GROUPING SETS explícita. Por exemplo, GROUP BY Column1, (Column2, ...ColumnN) é permitido no padrão, mas não no SQL Server. GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) ou GROUP BY Column1, Column2, ... ColumnN é permitido. Esses são semanticamente equivalentes ao exemplo GROUP BY anterior. Isso é para evitar a possibilidade de que GROUP BY Column1, (Column2, ...ColumnN) possa ser mal-interpretado como GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)). Isso não é semanticamente equivalente.

• 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 SQL-2006 padrão, mas não no SQL Server. O SQL Server 2008 e versões posteriores permitem GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) ou GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). Esses exemplos são semanticamente equivalentes ao primeiro exemplo de GROUP BY e têm uma sintaxe mais clara.

• GROUP BY [ALL/DISTINCT] não é permitido em uma cláusula geral de GROUP BY ou com as construções GROUPING SETS, ROLLUP, CUBE, WITH CUBE ou WITH ROLLUP. ALL é o padrão e está implícito.

Comparação de recursos GROUP BY com suporte

A tabela a seguir descreve recursos GROUP BY com suporte com base na versão do SQL Server e no nível de compatibilidade do banco de dados.

Recurso	SQL Server 2005 Integration Services	Nível 100 de compatibilidade do SQL Server 2008	Nível 90 de compatibilidade do SQL Server 2008 ou versões anteriores
Agregações de DISTINCT	Não há suporte para WITH CUBE ou WITH ROLLUP.	Há suporte para WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE ou ROLLUP.	O mesmo que o nível de compatibilidade 100 do SQL Server 2008.
Função definida pelo usuário com nome CUBE ou ROLLUP na cláusula GROUP BY	A função definida pelo usuário dbo.cube(arg1)...argN ou dbo.rollup(arg1,...argN) na cláusula GROUP BY é permitida. Por exemplo: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);	A função definida pelo usuário dbo.cube (arg1,... argN) ou dbo.rollup(arg1)...argN na cláusula GROUP BY não é permitida. Por exemplo: SELECT SUM (x) FROM T GROUP BY dbo.cube(y); A seguinte mensagem de erro é retornada: "Sintaxe incorreta próxima à palavra-chave 'cube'|'rollup'". Para evitar esse problema, substitua dbo.cube por [dbo].[cube] ou dbo.rollup por [dbo].[rollup]. O seguinte exemplo é permitido: SELECT SUM (x) FROM T GROUP BY [dbo].[cube](y);	A função definida pelo usuário dbo.cube (arg1,...argN) ou dbo.rollup(arg1,...argN) na cláusula GROUP BY é permitida Por exemplo: SELECT SUM (x) FROM T GROUP BY dbo.cube(y);
GROUPING SETS	Sem suporte	Com suporte	Com suporte
CUBE	Sem suporte	Com suporte	Sem suporte
ROLLUP	Sem suporte	Com suporte	Sem suporte
Total geral, como GROUP BY ()	Sem suporte	Com suporte	Com suporte
Função GROUPING_ID	Sem suporte	Com suporte	Com suporte
Função GROUPING	Com suporte	Com suporte	Com suporte
WITH CUBE	Com suporte	Com suporte	Com suporte
WITH ROLLUP	Com suporte	Com suporte	Com suporte
Remoção de agrupamento "duplicado" WITH CUBE ou WITH ROLLUP	Com suporte	Com suporte	Com suporte

Exemplos

Para obter exemplos que usam GROUPING SETS, ROLLUP e CUBE, consulte Usando GROUP BY com ROLLUP, CUBE e GROUPING SETS.

A. Usando uma cláusula simples GROUP BY

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

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

B. Usando 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.

USE AdventureWorks2008R2;
GO
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. Usando 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. A mesma expressão deve estar presente na lista SELECT e na cláusula GROUP BY.

USE AdventureWorks2008R2;
GO
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. Usando uma cláusula GROUP BY com uma cláusula HAVING

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

USE AdventureWorks2008R2;
GO
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);

Consulte também

Referência

GROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

Cláusula SELECT (Transact-SQL)

Conceitos

Usando GROUP BY com ROLLUP, CUBE e GROUPING SETS

Equivalentes GROUPING SETS

Solucionando problemas de erros de GROUP BY