GROUPING_ID (Transact-SQL)

É uma função que computa o nível de agrupamento. GROUPING_ID pode ser usada apenas na lista SELECT <select>, na cláusula HAVING ou ORDER BY, quando GROUP BY for especificado.

Convenções de sintaxe Transact-SQL

Sintaxe

GROUPING_ID ( <column_expression>[ ,...n ] )

Argumentos

• <column_expression>
  É uma column_expression em uma cláusula GROUP BY.

Tipo de retorno

int

Comentários

A <column_expression de GROUPING_ID> deve corresponder exatamente à expressão na lista de GROUP BY. Por exemplo, se você estiver agrupando por DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); ou, se estiver agrupando por <column name>, use GROUPING_ID (<column name>).

Comparando GROUPING_ID () com GROUPING ()

GROUPING_ID (<column_expression> [ ,...n ]) ]) inclui entradas equivalentes ao retorno de GROUPING (<column_expression>) para cada coluna em sua lista de colunas em cada linha de saída como uma cadeia de caracteres com números um e zero. GROUPING_ID interpreta a cadeia de caracteres como um número base 2 e retorna o inteiro equivalente. Por exemplo, considere a seguinte instrução: SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>. A tabela a seguir mostra os valores de entrada e saída de GROUPING_ID ().

Colunas agregadas	Entrada de GROUPING_ID (a, b, c) = GROUPING(a) + GROUPING(b) + GROUPING(c)	Saída de GROUPING_ID ()
a	100	4
b	010	2
c	001	1
ab	110	6
ac	101	5
bc	011	3
abc	111	7

Definição técnica de GROUPING_ID ()

Cada argumento GROUPING_ID deve ser um elemento da lista GROUP BY. GROUPING_ID () retorna um bitmap integer cujos menores bits N podem ser literais. Um bit literal indica que o argumento correspondente não é uma coluna de agrupamento para a linha de saída determinada. O bit da ordem mais baixa corresponde ao argumento N e o bitth N-1 de ordem mais baixa corresponde ao argumento 1.

Equivalentes de GROUPING_ID ()

Para uma única consulta de agrupamento, GROUPING (<column_expression>) é equivalente a GROUPING_ID (<column_expression>) e ambos retornam 0.

Por exemplo, as instruções seguintes são equivalentes:

SELECT GROUPING_ID(A,B) FROM T GROUP BY CUBE(A,B)

SELECT 3 FROM T GROUP BY () UNION ALL SELECT 1 FROM T GROUP BY A UNION ALL SELECT 2 FROM T GROUP BY B UNION ALL SELECT 0 FROM T GROUP BY A,B

Exemplos

A. Usando GROUPING_ID para identificar níveis de agrupamento

O exemplo a seguir retorna a contagem de funcionários pelo Name e Title, Name, e total da empresa. GROUPING_ID() é usado para criar um valor para cada linha na coluna Title que identifica seu nível de agregação.

USE AdventureWorks;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
    WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Title'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title);

B. Usando GROUPING_ID para filtrar um conjunto de resultados

Exemplo simples

No código a seguir, para retornar somente as linhas com uma contagem de funcionários por título, remova os caracteres de comentário de HAVING GROUPING_ID(D.Name, E.Title); = 0. Para retornar somente as linhas com uma contagem de funcionários por departamento, remova os caracteres de comentário de HAVING GROUPING_ID(D.Name, E.Title) = 1;.

USE AdventureWorks;
GO
SELECT D.Name
    ,E.Title
    ,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 1; --Group by Name

Veja o conjunto dos resultados não filtrados:

Nome	Título	Nível de agrupamento	Contagem de funcionários	Nome
Controle de documentos	Especialista de controle	0	2	Controle de documentos
Controle de documentos	Assistente de controle de documentos	0	2	Controle de documentos
Controle de documentos	Gerente de controle de documentos	0	1	Controle de documentos
Controle de documentos	NULL	1	5	Controle de documentos
Instalações e manutenção	Assistente administrativo de instalações	0	1	Instalações e manutenção
Instalações e manutenção	Gerente de instalações	0	1	Instalações e manutenção
Instalações e manutenção	Zelador	0	4	Instalações e manutenção
Instalações e manutenção	Supervisor de manutenção	0	1	Instalações e manutenção
Instalações e manutenção	NULL	1	7	Instalações e manutenção
NULL	NULL	3	12	NULL

Exemplo complexo

No exemplo a seguir, GROUPING_ID() é usado para filtrar um conjunto de resultados que contém vários níveis de agrupamento por nível de agrupamento. Um código semelhante pode ser usado para criar uma exibição com vários níveis de agrupamento e um procedimento armazenado que chama a exibição passando um parâmetro para filtrá-la por nível de agrupamento.

USE AdventureWorks;
GO
DECLARE @Grouping nvarchar(50);
DECLARE @GroupingLevel smallint;
SET @Grouping = N'CountryRegionCode Total';

SELECT @GroupingLevel = (
    CASE @Grouping
        WHEN N'Grand Total'             THEN 15
        WHEN N'SalesPerson Total'       THEN 14
        WHEN N'Store Total'             THEN 13
        WHEN N'Store SalesPerson Total' THEN 12
        WHEN N'CountryRegionCode Total' THEN 11
        WHEN N'Group Total'             THEN 7
        ELSE N'Unknown'
    END);

SELECT 
    T.[Group]
    ,T.CountryRegionCode
    ,S.Name AS N'Store'
    ,(SELECT C.FirstName + ' ' + C.LastName 
        FROM Person.Contact C 
        WHERE C.ContactId = H.SalesPersonID)
        AS N'Sales Person'
    ,SUM(TotalDue)AS N'TotalSold'
    ,CAST(GROUPING(T.[Group])AS char(1)) + 
        CAST(GROUPING(T.CountryRegionCode)AS char(1)) + 
        CAST(GROUPING(S.Name)AS char(1)) + 
        CAST(GROUPING(H.SalesPersonID)AS char(1)) 
        AS N'GROUPING base-2'
    ,GROUPING_ID((T.[Group])
        ,(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
        ) AS N'GROUPING_ID'
    ,CASE 
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 15 THEN N'Grand Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 14 THEN N'SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 13 THEN N'Store Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 12 THEN N'Store SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 11 THEN N'CountryRegionCode Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) =  7 THEN N'Group Total'
        ELSE N'Error'
        END AS N'Level'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
GROUP BY GROUPING SETS ((S.Name,H.SalesPersonID)
    ,(H.SalesPersonID),(S.Name)
    ,(T.[Group]),(T.CountryRegionCode),()
    )
HAVING GROUPING_ID(
    (T.[Group]),(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
    ) = @GroupingLevel
ORDER BY 
    GROUPING_ID(S.Name,H.SalesPersonID),GROUPING_ID((T.[Group])
    ,(T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID))ASC;

C. Usando GROUPING_ID () com ROLLUP e CUBE para identificar níveis de agrupamento

O código nos exemplos a seguir mostram o uso de GROUPING() para computar a coluna Bit Vector(base-2). GROUPING_ID() é usado para computar a coluna Integer Equivalent correspondente. A ordem de colunas na função GROUPING_ID() é o oposto da ordem de coluna das colunas que estão concatenadas pela função GROUPING().

Nesses exemplos, GROUPING_ID() é usado para criar um valor para cada linha na coluna Grouping Level ao identificar o nível de agrupamento. Os níveis de agrupando nem sempre são uma lista consecutiva de inteiros que começam com 1 (0, 1, 2,...n).

Observação
GROUPING e GROUPING_ID podem ser usados em uma cláusula HAVING para filtrar um conjunto de resultados.

Exemplo de ROLLUP

Neste exemplo, todos os níveis de agrupamento não aparecem como no exemplo de CUBE a seguir. Se a ordem das colunas na lista ROLLUP for alterada, os valores de nível na coluna Grouping Level também terão de ser alterados.

USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
     AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY ROLLUP(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm,OrderDate)
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

Veja um conjunto parcial de resultados.

Ano	Mês	Dia	Total devido	Vetor de bit (base 2)	Equivalente inteiro	Nível de agrupamento
2003	1	1	1762381	000	0	Ano Mês Dia
2003	1	2	21772.35	000	0	Ano Mês Dia
2003	2	1	3185233	000	0	Ano Mês Dia
2003	2	2	21684.41	000	0	Ano Mês Dia
2004	1	1	2239208	000	0	Ano Mês Dia
2004	1	2	46458.07	000	0	Ano Mês Dia
2004	2	1	3653194	000	0	Ano Mês Dia
2004	2	2	54598.55	000	0	Ano Mês Dia
2003	1	NULL	1784153	100	1	Ano Mês
2003	2	NULL	3206917	100	1	Ano Mês
2004	1	NULL	2285666	100	1	Ano Mês
2004	2	NULL	3707793	100	1	Ano Mês
2003	NULL	NULL	4991070	110	3	Ano
2004	NULL	NULL	5993459	110	3	Ano
NULL	NULL	NULL	10984529	111	7	Total geral

Exemplo de CUBE

Neste exemplo, a função GROUPING_ID() é usada para criar um valor para cada linha na coluna Grouping Level ao identificar o nível de agrupamento.

Diferentemente de ROLLUP no exemplo anterior, CUBE gera a saída de todos os níveis de agrupamento. Se a ordem das colunas na lista CUBE for alterada, os valores de nível na coluna Grouping Level também terão de ser alterados.

USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
        AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'Year Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY CUBE(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

Veja um conjunto parcial de resultados.

Ano	Mês	Dia	Total devido	Vetor de bit (base 2)	Equivalente inteiro	Nível de agrupamento
2003	1	1	1762381	000	0	Ano Mês Dia
2003	1	2	21772.35	000	0	Ano Mês Dia
2003	2	1	3185233	000	0	Ano Mês Dia
2003	2	2	21684.41	000	0	Ano Mês Dia
2004	1	1	2239208	000	0	Ano Mês Dia
2004	1	2	46458.07	000	0	Ano Mês Dia
2004	2	1	3653194	000	0	Ano Mês Dia
2004	2	2	54598.55	000	0	Ano Mês Dia
2003	1	NULL	1784153	100	1	Ano Mês
2003	2	NULL	3206917	100	1	Ano Mês
2004	1	NULL	2285666	100	1	Ano Mês
2004	2	NULL	3707793	100	1	Ano Mês
2003	NULL	1	4947613	010	2	Ano Dia
2003	NULL	2	43456.76	010	2	Ano Dia
2004	NULL	1	5892402	010	2	Ano Dia
2004	NULL	2	101056.6	010	2	Ano Dia
2003	NULL	NULL	4991070	110	3	Ano
2004	NULL	NULL	5993459	110	3	Ano
NULL	1	1	4001589	001	4	Mês Dia
NULL	1	2	68230.42	001	4	Mês Dia
NULL	2	1	6838427	001	4	Mês Dia
NULL	2	2	76282.96	001	4	Mês Dia
NULL	1	NULL	4069819	101	5	Mês
NULL	2	NULL	6914710	101	5	Mês
NULL	NULL	1	10840016	011	6	Dia
NULL	NULL	2	144513.4	011	6	Dia
NULL	NULL	NULL	10984529	111	7	Total geral

Consulte também

Referência

GROUPING (Transact-SQL)

GROUP BY (Transact-SQL)

Conceitos

Usando GROUP BY com ROLLUP, CUBE e GROUPING SETS

Equivalentes GROUPING SETS

Solucionando problemas de erros de GROUP BY