CASE Statements — DataLab

CASE statements are equal to If/Then statements, you need WHEN THEN conditions, and the query needs to end with an END statement and a new column heading where the statement will land.

DataFrameas

df

variable

SELECT id, home_goal, away_goal
	CASE WHEN home_goal > away_goal THEN 'Home Team Win'
	WHEN home_goal < away_goal THEN 'Away Team Win'
	ELSE 'Tie' END AS outcome 
FROM match 
WHERE season = '2013/2014';

Create a list of matches in the 2011/2012 season where Barcelona was the home team. You will do this using a CASE statement that compares the values of two columns to create a new group -- wins, losses, and ties.

Filter for matches where the home team is FC Barcelona (id = 8634).

DataFrameas

df1

variable

SELECT 
	m.date,
	t.team_long_name AS opponent,
	CASE WHEN m.home_goal > m.away_goal THEN 'Barcelona win!'
        WHEN m.home_goal < m.away_goal THEN 'Barcelona loss :(' 
        ELSE 'Tie' END AS outcome 
FROM matches_spain AS m
LEFT JOIN teams_spain AS t 
ON m.awayteam_id = t.team_api_id
WHERE m.hometeam_id = 8634;

DataFrameas

df2

variable