Get startedGet started for free

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

View Course

Exercise instructions

  • Create a CASE WHEN statement counting the matches played in the "2012/2013" season.
  • Create a CASE WHEN statement counting the matches played in the"2013/2014" season, aliasing as matches_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;
Edit and Run Code