Calculating percent with CASE and AVG
CASE statements will return any value you specify in your THEN clause. This is an incredibly powerful tool for robust calculations and data manipulation when used in conjunction with an aggregate statement. One key task you can perform is using CASE inside an AVG function to calculate a percentage of information in your database.
Here's an example of how you set that up:
AVG(CASE WHEN condition_is_met THEN 1
WHEN condition_is_not_met THEN 0 END)
With this approach, it's important to accurately specify which records count as 0, otherwise your calculations may not be correct!
Your task is to examine the number of wins, losses, and ties in each country. The matches table is filtered to include all matches from the 2013/2014 and 2014/2015 seasons.
This exercise is part of the course
Data Manipulation in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
c.name AS country,
-- Count the home wins, away wins, and ties in each country
___(___ ___ m.home_goal ___ m.away_goal THEN m.id
END) AS home_wins,
___(___ ___ m.home_goal ___ m.away_goal THEN m.id
END) AS away_wins,
___(___ ___ m.home_goal ___ m.away_goal THEN m.id
END) AS ties
FROM country AS c
LEFT JOIN matches AS m
ON c.id = m.country_id
GROUP BY country;