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 SQL

Introduction
GROUP BY – Recap
ROLLUP
Summary

Instruction

When using multiple columns inside ROLLUP's parentheses, it's quite easy to get lost among the resulting rows. SQL offers a 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
  full_name,
  category, 
  week,
  AVG(score) AS avg_score,
  GROUPING(full_name) AS F,
  GROUPING(category) AS C, 
  GROUPING(week) AS W
FROM contest_score
GROUP BY 
  ROLLUP (full_name, 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.