Fixing calculations with coalesce
Null values impact aggregations in a number of ways. One issue is related to the AVG() function. By default, the AVG() function does not take into account any null values. However, there may be times when you want to include these null values in the calculation as zeros.
To replace null values with a string or a number, use the COALESCE() function. Syntax is COALESCE(fieldName,replacement), where replacement is what should replace all null instances of fieldName.
This exercise will walk you through why null values can throw off calculations and how to troubleshoot these issues.
This exercise is part of the course
Reporting in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Pull events and golds by athlete_id for summer events
SELECT
____,
____ AS total_events,
____ AS gold_medals
FROM ____
GROUP BY ____
-- Order by total_events descending and athlete_id ascending
ORDER BY ____;