COUNT using CASE WHEN
Do the number of soccer matches played in a given European country differ across seasons? We will use the European Soccer Database to answer this question.
You will examine the number of matches played in 3 seasons within each country listed in the database. This is much easier to explore with each season's matches in separate columns. Using the country and unfiltered match table, you will count the number of matches played in each country during the 2012/2013 and 2013/2014 seasons.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Create a
CASE WHENstatement counting the matches played in the"2012/2013"season. - Create a
CASE WHENstatement counting the matches played in the"2013/2014"season, aliasing asmatches_2013_2014.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
c.name AS country,
-- Count matches in 2012/13
___(___ ___ m.season = '___' ___ m.id END) AS matches_2012_2013,
-- Count matches in 2013/14
___(___ ___ m.season = '___' ___ m.id ___) ___ ___
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;