Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

GROUP BY Extensions in MS SQL Server

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.