Share via


GROUPING_ID (Transact-SQL)

Is a function that computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified.

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Arguments

  • <column_expression>
    Is a column_expression in a GROUP BY clause.

Return Type

int

Remarks

The GROUPING_ID <column_expression> must exactly match the expression in the GROUP BY list. For example, if you are grouping by DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); or if you are grouping by <column name>, use GROUPING_ID (<column name>).

Comparing GROUPING_ID () to GROUPING ()

GROUPING_ID (<column_expression> [ ,...n ]) inputs the equivalent of the GROUPING (<column_expression>) return for each column in its column list in each output row as a string of ones and zeros. GROUPING_ID interprets that string as a base-2 number and returns the equivalent integer. For example consider the following statement: SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>. The following table shows the GROUPING_ID () input and output values.

Columns aggregated

GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c)

GROUPING_ID () output

a

100

4

b

010

2

c

001

1

ab

110

6

ac

101

5

bc

011

3

abc

111

7

Technical Definition of GROUPING_ID ()

Each GROUPING_ID argument must be an element of the GROUP BY list. GROUPING_ID () returns an integer bitmap whose lowest N bits may be lit. A lit bit indicates the corresponding argument is not a grouping column for the given output row. The lowest-order bit corresponds to argument N, and the N-1th lowest-order bit corresponds to argument 1.

GROUPING_ID () Equivalents

For a single grouping query, GROUPING (<column_expression>) is equivalent to GROUPING_ID (<column_expression>), and both return 0.

For example, the following statements are equivalent:

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

Examples

A. Using GROUPING_ID to identify grouping levels

The following example returns the count of employees by Name and Title, Name, and company total. GROUPING_ID() is used to create a value for each row in the Title column that identifies its level of aggregation.

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. Using GROUPING_ID to filter a result set

Simple Example

In the following code, to return only the rows that have a count of employees by title, remove the comment characters from HAVING GROUPING_ID(D.Name, E.Title); = 0. To return only rows with a count of employees by department, remove the comment characters from 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

Here is the unfiltered result set.

Name

Title

Grouping Level

Employee Count

Name

Document Control

Control Specialist

0

2

Document Control

Document Control

Document Control Assistant

0

2

Document Control

Document Control

Document Control Manager

0

1

Document Control

Document Control

NULL

1

5

Document Control

Facilities and Maintenance

Facilities Administrative Assistant

0

1

Facilities and Maintenance

Facilities and Maintenance

Facilities Manager

0

1

Facilities and Maintenance

Facilities and Maintenance

Janitor

0

4

Facilities and Maintenance

Facilities and Maintenance

Maintenance Supervisor

0

1

Facilities and Maintenance

Facilities and Maintenance

NULL

1

7

Facilities and Maintenance

NULL

NULL

3

12

NULL

Complex Example

In the following example, GROUPING_ID() is used to filter a result set that contains multiple grouping levels by grouping level. Similar code can be used to create a view that has several grouping levels and a stored procedure that calls the view by passing a parameter that filters the view by grouping level.

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. Using GROUPING_ID () with ROLLUP and CUBE to identify grouping levels

The code in the following examples show using GROUPING() to compute the Bit Vector(base-2) column. GROUPING_ID() is used to compute the corresponding Integer Equivalent column. The column order in the GROUPING_ID() function is the opposite of the column order of the columns that are concatenated by the GROUPING() function.

In these examples, GROUPING_ID() is used to create a value for each row in the Grouping Level column to identify the level of grouping. Grouping levels are not always a consecutive list of integers that start with 1 (0, 1, 2,...n).

Note

GROUPING and GROUPING_ID can be used n a HAVING clause to filter a result set.

ROLLUP Example

In this example, all grouping levels do not appear as they do in the following CUBE example. If the order of the columns in the ROLLUP list is changed, the level values in the Grouping Level column will also have to be changed.

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

Here is a partial result set.

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2003

1

1

1762381

000

0

Year Month Day

2003

1

2

21772.35

000

0

Year Month Day

2003

2

1

3185233

000

0

Year Month Day

2003

2

2

21684.41

000

0

Year Month Day

2004

1

1

2239208

000

0

Year Month Day

2004

1

2

46458.07

000

0

Year Month Day

2004

2

1

3653194

000

0

Year Month Day

2004

2

2

54598.55

000

0

Year Month Day

2003

1

NULL

1784153

100

1

Year Month

2003

2

NULL

3206917

100

1

Year Month

2004

1

NULL

2285666

100

1

Year Month

2004

2

NULL

3707793

100

1

Year Month

2003

NULL

NULL

4991070

110

3

Year

2004

NULL

NULL

5993459

110

3

Year

NULL

NULL

NULL

10984529

111

7

Grand Total

CUBE Example

In this example, the GROUPING_ID() function is used to create a value for each row in the Grouping Level column to identify the level of grouping.

Unlike ROLLUP in the previous example, CUBE outputs all grouping levels. If the order of the columns in the CUBE list is changed, the level values in the Grouping Level column will also have to be changed.

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

Here is a partial result set.

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2003

1

1

1762381

000

0

Year Month Day

2003

1

2

21772.35

000

0

Year Month Day

2003

2

1

3185233

000

0

Year Month Day

2003

2

2

21684.41

000

0

Year Month Day

2004

1

1

2239208

000

0

Year Month Day

2004

1

2

46458.07

000

0

Year Month Day

2004

2

1

3653194

000

0

Year Month Day

2004

2

2

54598.55

000

0

Year Month Day

2003

1

NULL

1784153

100

1

Year Month

2003

2

NULL

3206917

100

1

Year Month

2004

1

NULL

2285666

100

1

Year Month

2004

2

NULL

3707793

100

1

Year Month

2003

NULL

1

4947613

010

2

Year Day

2003

NULL

2

43456.76

010

2

Year Day

2004

NULL

1

5892402

010

2

Year Day

2004

NULL

2

101056.6

010

2

Year Day

2003

NULL

NULL

4991070

110

3

Year

2004

NULL

NULL

5993459

110

3

Year

NULL

1

1

4001589

001

4

Month Day

NULL

1

2

68230.42

001

4

Month Day

NULL

2

1

6838427

001

4

Month Day

NULL

2

2

76282.96

001

4

Month Day

NULL

1

NULL

4069819

101

5

Month

NULL

2

NULL

6914710

101

5

Month

NULL

NULL

1

10840016

011

6

Day

NULL

NULL

2

144513.4

011

6

Day

NULL

NULL

NULL

10984529

111

7

Grand Total