SQL and CASE WHEN Statements

Leave a reply
Ads

CASE WHEN statements are the SQL version of If-Then statements. Essentially, SQL will perform certain behaviors based on the the criteria you set at the data analyst. In this post, we will go through several examples of the use of CASE WHEN statements using the NBA_salary database that is available on Kaggle.

Example 1

In the first example, we are going to use a CASE WHEN statement to identify if a basketball player is a shooting guard or not below is the code followed by the output.

SELECT Pos,Tm,
CASE WHEN Pos = 'SG' THEN 'Yes' END AS Shooting_Guard
FROM Seasons_Stats

In line one we SELECT two columns which are Pos and Tm. In the second line is the CASE WHEN statement. We are telling SQL that when Pos = ‘SG’ then place a yes in a new column called Shooting_Guard. You can see in the second row that there is a shooting guard there and this is why there is a yes. All the other rows have a NULL there. In the next example we will learn how to deal with the NULL values.

Example 2

In the second example we will replace the NULL values with the word No. Below is the code and output 

SELECT Pos,Tm,
CASE WHEN Pos = 'SG' THEN 'Yes'ELSE 'No' END AS Shooting_Guard
FROM Seasons_Stats

The only change to the code is found in the second line. We added the ELSE command and place the word No in single quotes. This tells SQL that if a player is not a shooting guard then put the word No in the new column.

Example 3

In this example we are going to employ an OR command. As with If-Then statements, CASE WHEN Statements can use AND, OR, IN commands as well. Below we are going to have SQL say yes if a player is a shooting guard or a member of the CHS team. 

SELECT Pos,Tm,
CASE WHEN Pos = 'SG' OR Tm = 'CHS' THEN 'Yes'ELSE 'No' END AS Shooting_Guard
FROM Seasons_Stats

The only change is found in line 2 again. We added the OR command and told SQL to say yes if Tm = CHS. This why if you look at the results we have a yes in row 3. Even though this player is a SF SQL still marked yes because he was on the CHS team.

Example 4

In our last example, we are going to make several categories by using several WHEN statements. Specifically, we are going to divide players based on how many points they scored. Below is the code and output.

SELECT PTS, 
CASE WHEN PTS > 2500 THEN 'Over 2500'
	WHEN PTS > 1500 AND PTS < 2499 THEN 'Over 1500'
	WHEN PTS > 500 AND PTS < 1499 THEN 'Over 500'
	ELSE 'Less than 500' END AS Pts_groups
FROM Seasons_Stats

Here is an explanation

  1. We call the PTS column
  2. We create our CASE WHEN statements
    • If a player scored more than 2500 then say ‘Over 2500’
    • If a player scored more than 1500 and less then 2499 the say ‘Over 1500’
    • If a player scored more than 500 and less then 1499 the say ‘Over 500’
    • If a player scored less than 1500 then say ‘Less than 500’

Conclusion

This was just several simple examples of the use of CASE WHEN statements. The purpose of this command is to perform certain actions when certain criteria are set. When this happens powerful things can be done in SQL.

Leave a Reply