Introduction
GROUP BY – Recap
ROLLUP
Summary

Instruction

Great! SQL Server offers an alternative function that tells you if the column is included in the grouping: GROUPING().

The GROUPING() function takes one column as an argument. It returns a 0 if the column is used in the grouping and a 1 if it is not. Take a look:

SELECT
  FullName,
  Category, 
  Week,
  AVG(Score) AS AvgScore,
  GROUPING(FullName) AS F,
  GROUPING(Category) AS C, 
  GROUPING(Week) AS W
FROM ContestScore
GROUP BY 
  ROLLUP (FullName, Category, Week);

As you can see, we added three GROUPING() functions in our SELECT clause. Inside the parentheses, we put all columns from the parentheses of ROLLUP. Let's see what the result is.

Exercise

Run the template query. As you can see, integer numbers appear in the final columns, denoting the grouping level for a given row.