CASE WHEN in SQL (IF ELSE in SQL)

What if you want to create a new variable using if-else conditions or want to do some aggregations based on some if-else conditions? SQL is so powerful that its CASE WHEN syntax provides you this utility.

Prerequisites:

WHERE: Click here to review the tutorial

Aggregate functions and GROUP BY: Click here to review the tutorial

Data:

For this tutorial we shall make use of the data: hospital_record

CREATE TABLE hospital_record(
patient_id int,
department varchar(20),
length_of_stay int,
last_visit_date date)

INSERT INTO
hospital_record(patient_id,department,length_of_stay,last_visit_date)
VALUES
(1001,'Cardiology',20,'12Aug2020'),
(1002,'Radiology',0,'13Feb2019'),
(1004,'Radiology',2,'31Jan2020'),
(1005,'Neurology',4,'9Aug2018'),
(1005,'Cardiology',18,'9Aug2018'),
(1004,'Neurology',65,'19Jan2020');

Our data looks as follows:

SELECT * FROM hospital_record;

Basic Syntax for CASE WHEN:

CASE WHEN <condition 1> THEN <do something> WHEN <condition 2> THEN <do something> ELSE <default value> END AS new_column

Creating a new column with CASE WHEN:

Task: Create a new column Stay_Duration based on the conditions:

if length of stay = 0 then One day visit, if length of stay for 1-15 days then Less than a fortnight and if length of stay is more than 15 days then More than a fortnight.

SELECT *,CASE WHEN length_of_stay = 0 THEN 'One day visit'
WHEN length_of_Stay <= 15 THEN 'Less than a fortnight'
ELSE 'More than a fortnight' END AS Stay_Duration 
FROM hospital_record;

Our CASE WHEN block always comes with SELECT statement while creating a new column.

Task: Create Stay_Duration variable only fetch rows only for the Cardiology department.

SELECT *,CASE WHEN length_of_stay = 0 THEN 'One day visit'
WHEN length_of_Stay <= 15 THEN 'Less than a fortnight'
ELSE 'More than a fortnight' END AS Stay_Duration 
FROM hospital_record
WHERE department = 'Cardiology';

Multiple conditions in CASE WHEN

Task: Create Stay_Duration variable : If department = 'Radiology' or length of stay = 0 then One day visit, if length of stay for 1-15 days then Less than a fortnight and if length of stay is more than 15 days then More than a fortnight.

SELECT *,
CASE WHEN length_of_stay = 0 OR department = 'Radiology' THEN 'One day visits'
WHEN length_of_Stay <= 15 THEN 'Less than a fortnight'
ELSE 'More than a fortnight' END AS Stay_Duration 
FROM hospital_record

What if you skip the ELSE clause? (Dealing with NULL values)

If you skip the ELSE clause then NULL values can get generated like given below:

Task: Create a new column Stay_Duration based on the conditions: if length of stay = 0 then One day visit, if length of stay for 1-15 days then Less than a fortnight

SELECT *,CASE WHEN length_of_stay = 0  THEN 'One day visits'
WHEN length_of_Stay <= 15 THEN 'Less than a fortnight'
ELSE NULL END AS Stay_Duration 
FROM hospital_record

Alternatively

select *,CASE WHEN length_of_stay = 0  THEN 'One day visits'
WHEN length_of_Stay <= 15 THEN 'Less than a fortnight'
END AS Stay_Duration 
FROM hospital_record

Ques: How to deal with these NULL values? What if we need to remove these NULL records? Ans: Add a WHERE clause and put your CASE WHEN statement, after END write IS NOT NULL.

SELECT *,CASE WHEN length_of_stay = 0  THEN 'One day visits'
WHEN length_of_Stay <= 15 THEN 'Less than a fortnight'
END AS Stay_Duration 
FROM hospital_record
WHERE CASE WHEN length_of_stay = 0  THEN 'One day visits'
WHEN length_of_Stay <= 15 THEN 'Less than a fortnight'
END IS NOT NULL

CASE WHEN and aggregate functions:

Task: For each department count the number of one_day_visits and more than one day visits

SELECT department,COUNT(CASE WHEN length_of_stay = 0  THEN 'One day visits' END) as one_day_visits_count,
COUNT(CASE WHEN length_of_Stay > 0 THEN 'Less than a fortnight' END) as long_visits_count
FROM hospital_record
GROUP BY department

Since we are aggregating at department level thus a GROUP BY clause is mandatory. For counting the number of one day visits and long duration visits we leverage COUNT function and specify the condition in CASE WHEN). Kindly note that COUNT function only counts non-NULL values.

Alternatively, since we just need to COUNT the occurence thus in THEN clause you can add any junk value.

SELECT department,COUNT(CASE WHEN length_of_stay = 0  THEN 'xyz' END) as one_day_visits_count,
COUNT(CASE WHEN length_of_Stay > 0 THEN 'xyz' END) as long_visits_count
FROM hospital_record
GROUP BY department

Task: For each department calculate the total length of stay by all the patients for one_day_visits and more.

SELECT department,SUM(CASE WHEN length_of_stay = 0  THEN 1 END) as one_day_visits_sum,
SUM(CASE WHEN length_of_Stay > 0 THEN length_of_Stay END) as long_visits_sum
FROM hospital_record
GROUP BY department