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) 
