Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
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(full_name, 'All Contestants') AS full_name,
  COALESCE(category, 'All Categories') AS category,
  week,
  AVG(score) AS avg_score
FROM contest_score
GROUP BY ROLLUP(full_name, category), week;

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

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

Exercise

Run the template query and see how it works.