SQL Server has a GROUPING_ID() function that returns the level of grouping based on its arguments.

We pass one or more column expressions to the function, and it computes the level of grouping. The column expressions must match the ones provided by the GROUP BY clause.

Example

Suppose we have the following table:

SELECT * FROM Dogs;

Result:

DogName  Activity     Score
-------  -----------  -----
Bruno    Fetch Stick  43   
Cooper   Fetch Stick  43   
Max      Fetch Stick  91   
Bruno    Wag Tail     65   
Cooper   Wag Tail     51   
Max      Wag Tail     87   
Bruno    Keep Quiet   3    
Cooper   Keep Quiet   null 
Max      Keep Quiet   1    
null     Catch Cat    null 

This is a table of dogs and their scores at performing various activities.

Here’s an example of applying the GROUPING_ID() function to that table:

SELECT 
    Activity, 
    DogName, 
    SUM(Score) AS "Score", 
    GROUPING_ID(Activity, DogName) AS "Grouping ID"  
FROM Dogs  
GROUP BY ROLLUP (Activity, DogName);

Result:

Activity     DogName  Score  Grouping ID
-----------  -------  -----  -----------
Catch Cat    null     null   0          
Catch Cat    null     null   1          
Fetch Stick  Bruno    43     0          
Fetch Stick  Cooper   43     0          
Fetch Stick  Max      91     0          
Fetch Stick  null     177    1          
Keep Quiet   Bruno    3      0          
Keep Quiet   Cooper   null   0          
Keep Quiet   Max      1      0          
Keep Quiet   null     4      1          
Wag Tail     Bruno    65     0          
Wag Tail     Cooper   51     0          
Wag Tail     Max      87     0          
Wag Tail     null     203    1          
null         null     384    3          

Microsoft’s documentation explains how it works as follows:

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. 

Here’s a modified version of the above example that might help make it easier to understand:

SELECT 
    IIF(GROUPING(Activity) = 1, 'All Activities', Activity) AS "Activity", 
    IIF(GROUPING(DogName) = 1, 'All Dogs', DogName) AS "DogName",
    SUM(Score) AS "Score", 
    GROUPING_ID(Activity, DogName) AS "Grouping ID"  
FROM Dogs  
GROUP BY ROLLUP (Activity, DogName);

Result:

Activity        DogName   Score  Grouping ID
--------------  --------  -----  -----------
Catch Cat       null      null   0          
Catch Cat       All Dogs  null   1          
Fetch Stick     Bruno     43     0          
Fetch Stick     Cooper    43     0          
Fetch Stick     Max       91     0          
Fetch Stick     All Dogs  177    1          
Keep Quiet      Bruno     3      0          
Keep Quiet      Cooper    null   0          
Keep Quiet      Max       1      0          
Keep Quiet      All Dogs  4      1          
Wag Tail        Bruno     65     0          
Wag Tail        Cooper    51     0          
Wag Tail        Max       87     0          
Wag Tail        All Dogs  203    1          
All Activities  All Dogs  384    3          

This time we’ve replaced aggregate nulls with either All Dogs or All Activities, depending on which one the null represents. Any nulls in the underlying data remain at null.

So we can see that the last row contains the grand total score for all dogs across all activities. The other rows with All Dogs are subtotals.

Here’s the same query, except this time we replace ROLLUP with CUBE:

SELECT 
    IIF(GROUPING(Activity) = 1, 'All Activities', Activity) AS "Activity", 
    IIF(GROUPING(DogName) = 1, 'All Dogs', DogName) AS "DogName",
    SUM(Score) AS "Score", 
    GROUPING_ID(Activity, DogName) AS "Grouping ID"  
FROM Dogs  
GROUP BY CUBE (Activity, DogName);

Result:

Activity        DogName   Score  Grouping ID
--------------  --------  -----  -----------
Catch Cat       null      null   0          
All Activities  null      null   2          
Fetch Stick     Bruno     43     0          
Keep Quiet      Bruno     3      0          
Wag Tail        Bruno     65     0          
All Activities  Bruno     111    2          
Fetch Stick     Cooper    43     0          
Keep Quiet      Cooper    null   0          
Wag Tail        Cooper    51     0          
All Activities  Cooper    94     2          
Fetch Stick     Max       91     0          
Keep Quiet      Max       1      0          
Wag Tail        Max       87     0          
All Activities  Max       179    2          
All Activities  All Dogs  384    3          
Catch Cat       All Dogs  null   1          
Fetch Stick     All Dogs  177    1          
Keep Quiet      All Dogs  4      1          
Wag Tail        All Dogs  203    1          

The output of GROUPING_ID() has changed, due to the output of the CUBE option.

More Information

See Microsoft’s documentation for more information and more complex examples of the GROUPING_ID() function.