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'
.