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”
Exercise instructions
- Create 3
CASEstatements to "count" matches in the'2012/2013','2013/2014', and'2014/2015'seasons, respectively. - Have each
CASEstatement return a1for every match you want to include, and a0for every match to exclude. - Wrap the
CASEstatement in aSUMto 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 ___;This exercise is part of the course
Data Manipulation in SQL
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
In this chapter, you will learn how to use the CASE WHEN statement to create categorical variables, aggregate data into a single column with multiple filtering conditions, and calculate counts and percentages.
Exercise 1: We'll take the CASEExercise 2: Basic CASE statementsExercise 3: CASE statements comparing column valuesExercise 4: CASE statements comparing two column values part 2Exercise 5: In CASE things get more complexExercise 6: In CASE of rivalryExercise 7: Filtering your CASE statementExercise 8: CASE WHEN with aggregate functionsExercise 9: COUNT using CASE WHENExercise 10: COUNT and CASE WHEN with multiple conditionsExercise 11: Calculating percent with CASE and AVGWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.