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
Exercise instructions
- Update the
ON
statement in the subquery by adding a second field toJOIN
on. - If an error occurs related to the new
JOIN
field, use aCAST()
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;