Fixing duplication through a JOIN

In the previous exercise, you set up a query that contained duplication. This exercise will remove the duplication. One approach to removing duplication is to change the JOIN logic by adding another field to the ON statement.

The final query from last exercise is shown in the console. Your job is to fix the duplication by updating the ON statement. Note that the total gold_medals value should be 47.

Feel free to reference the E:R Diagram.

This exercise is part of the course

Reporting in SQL

View Course

Exercise instructions

Update the ON statement in the subquery by adding a second field to JOIN on.
If an error occurs related to the new JOIN field, use a CAST() statement to fix it.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT SUM(gold_medals) AS gold_medals
FROM
	(SELECT 
     	w.country_id, 
     	SUM(gold) AS gold_medals, 
     	AVG(gdp) AS avg_gdp
    FROM winter_games AS w
    JOIN country_stats AS c
    -- Update the subquery to join on a second field
    ON c.country_id = w.country_id ____
    GROUP BY w.country_id) AS subquery;

Edit and Run Code