1. Learn
    2. /
  2. Courses
    3. /
  3. Data Manipulation in SQL

Exercise

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.

Instructions

100 XP
  • 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.