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.
Questo esercizio fa parte del corso
Data Manipulation in SQL
Istruzioni dell'esercizio
- 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.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
SELECT
c.name AS country,
-- Count matches in 2012/2013
___(___ ___ m.season = '___' ___ m.id END) AS matches_2012_2013,
-- Count matches in 2013/2014
___(___ ___ m.season = '___' ___ m.id ___) ___ ___
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;