Introduction
GROUP BY – Recap
ROLLUP
Summary

Instruction

Perfect! The last thing we'll show you in this part is how to get rid of those nasty NULL values in higher grouping levels. To that end, we'll use the COALESCE() function.

SELECT
 COALESCE(FullName, N'All Contestants') AS FullName,
 COALESCE(Category, N'All Categories') AS Category,
 Week,
 AVG(Score) AS AvgScore
FROM ContestScore
GROUP BY ROLLUP(FullName, Category), Week;

COALESCE() takes as many arguments as you wish and returns the first element that is not NULL. For instance,

COALESCE(FullName, 'All Contestants')
will produce either the respective FullName value or the string 'All Contestants' if FullName is NULL. Naturally, you can use any other text value instead of 'All Contestants'.

Exercise

Run the template query and see how it works.