In SQL Server, the GROUPING() function is used to distinguish the null values that are returned by ROLLUP, CUBE, or GROUPING SETS from standard null values.

Basically it tells us whether a specified column expression in a GROUP BY list is aggregated or not.

The GROUPING() function returns either 1 or 0 (1 indicates that the column expression is aggregated and 0 indicates that it’s not).

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    

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

Here’s an example of using the GROUPING() function against that data:

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

Result:

Activity     Score  Grouping
-----------  -----  --------
Fetch Stick  177    0       
Keep Quiet   4      0       
Wag Tail     203    0       
null         384    1       

Here I used the SUM() aggregate function to calculate the total scores of each dog for each activity. I also used GROUP BY ROLLUP to provide a grand total of all the scores.

And I used the GROUPING() function to provide a column that indicates whether the specified column in the GROUP BY list is aggregated or not. In other words, it tells us whether the null value in the Activity column indicates that this is an aggregate.

The NULL returned by ROLLUP is a special use of NULL. As Microsoft’s documentation states:

The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. This acts as a column placeholder in the result set and means all.

So in our last row, the null value means “all”. And the TotalScore column contains a sum of the preceding rows. And because of this, the Grouping column contains 1.

Changing the null Value

We can modify our code to replace NULL with another value:

SELECT 
    IIF(GROUPING(Activity) = 1, 'Total', Activity) AS "Activity", 
    SUM(Score) AS "Score"  
FROM Dogs  
GROUP BY ROLLUP (Activity);

Result:

Activity     Score
-----------  -----
Fetch Stick  177  
Keep Quiet   4    
Wag Tail     203  
Total        384  

Here I passed GROUPING() to the IIF() function in order to evaluate whether or not GROUPING() returned 1 or 0. If 1, then the null value is replaced with Total. If 0, then the column’s contents is returned as normal.

Doing this can help to eliminate any confusion over the meaning of the null value in the results.

Adding Another Column

Here’s an example that includes two columns in the ROLLUP operation, as well as two instances of GROUPING() in the SELECT list:

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

Result:

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

Here NULL is replaced with a different message (either All Activities or All Dogs), depending on which column it concerns.