Is there a way of nesting case when statements when one has multiple conditions. For example below, where num_students = 2, there are many diff classes. Thus the else statement for num_of students =2 is 'Stu_2, but for the overall data is 'unk'
select id, test_a, test_b,
case when num_students = 5 then 'Stu_5'
when num_students = 4 then 'Stu_4'
when num_students = 3 then 'Stu_3'
when num_students = 2 and class = 'Eng' then 'Stu_Eng_2'
when num_students =2 and class = 'Fre' then 'Stu_Fre_2'
when num_students = 2 and class = 'His' then 'Stu_His_2'
when num_students =2 and class = 'Geo' then 'Stu_Geo_2'
else 'Stu_2'
else 'unk'
from table
2 Answers
Yep, that's one of the purposes of nesting case statements. When you nest them, you need to call the CASE statement again as follows:
select
id,
test_a,
test_b,
case
when num_students = 5 then 'Stu_5'
when num_students = 4 then 'Stu_4'
when num_students = 3 then 'Stu_3'
when num_students = 2 then
case
when class = 'Eng' then 'Stu_Eng_2'
when class = 'Fre' then 'Stu_Fre_2'
when class = 'His' then 'Stu_His_2'
when class = 'Geo' then 'Stu_Geo_2'
else 'Stu_2'
end
else 'unk'
end as <column_name>
from <table_name>
Don't forget the END clause after each nested CASE WHEN :)
You can phrase this in a different order:
(case when num_students = 5 then 'Stu_5'
when num_students = 4 then 'Stu_4'
when num_students = 3 then 'Stu_3'
when num_students <> 2 or num_students is null then 'unk'
-- num_students must be 2
when class = 'Eng' then 'Stu_Eng_2'
when class = 'Fre' then 'Stu_Fre_2'
when class = 'His' then 'Stu_His_2'
when class = 'Geo' then 'Stu_Geo_2'
else 'Stu_2'
end)
