Exercise

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.

Instructions 1/4

undefined XP
    1
    2
    3
    4
  • Build a report that shows total_events and gold_medals by athlete_id for all summer events, ordered by total_events descending then athlete_id ascending.