Filtering and totaling using CASE WHEN
You can use CASE
statements to apply a filter and perform a calculation, by writing the statement inside an aggregate function such as SUM()
!
In this exercise, your goal is to filter for a specific team (Real Sociedad) and calculate their total home and away goals per season.
This exercise is part of the course
Data Manipulation in SQL
Exercise instructions
- Create a
CASE
statement to calculate the total number of home goals where thehometeam_id
is8560
. - Create a second
CASE
statement to calculate the total number of away goals where theawayteam_id
is8560
, aliasing the column asaway_goals
. - Group the query by
season
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT season,
-- SUM the home goals
___(___ ___ hometeam_id = 8560 THEN ___ END) AS home_goals,
-- SUM the away goals
___
FROM match
-- Group the results by season
___