COUNT and CASE WHEN with multiple conditions

In R or Python, you have the ability to calculate a SUM of logical values (i.e., TRUE/FALSE) directly. In SQL, you have to convert these values into 1 and 0 before calculating a sum. This can be done using a CASE statement.

There's one key difference when using SUM to aggregate logical values compared to using COUNT in the previous exercise --

Your goal here is to use the country and match table to determine the total number of matches won by the home team in each country during the 2012/2013, 2013/2014, and 2014/2015 seasons.

This is a part of the course

“Data Manipulation in SQL”

View Course

Exercise instructions

Create 3 CASE statements to "count" matches in the '2012/2013', '2013/2014', and '2014/2015' seasons, respectively.
Have each CASE statement return a 1 for every match you want to include, and a 0 for every match to exclude.
Wrap the CASE statement in a SUM to return the total matches played in each season.
Group the query by the country name alias.

Hands-on interactive exercise

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

SELECT 
	c.name AS country,
    -- Sum the total records in each season where the home team won
	___(___ ___ m.season = '2012/2013' AND m.home_goal ___ m.away_goal 
        THEN ___ ELSE ___ ___) AS matches_2012_2013,
 	___(___ ___ m.season = '2013/2014' ___ ___ ___ ___ 
        THEN ___ ___ 0 ___) AS matches_2013_2014,
	___(___ ___ m.season = ___ ___ ___ ___ ___ 
        ___ 1 ___ 0 ___) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
-- Group by country name alias
GROUP BY ___;

Edit and Run Code