Save up to $499! Grab all Python courses for $49 or all online courses we’ve ever launched for only $169. Only Feb 11-16. Happy Valentine's!
GROUP BY – Recap


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.

 COALESCE(FullName, N'All Contestants') AS FullName,
 COALESCE(Category, N'All Categories') AS Category,
 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'.


Run the template query and see how it works.