Using the CASE statement on another derived column possible?

Hi everyone!
Are we able to use the case statement on another derived column in the result set?

e.g.

Base on the conditions below:
High if AVG(score) >=10
Medium if AVG(score) is between 10 and 5
Low for anything below or equal 5

I would like to have the following results:
Result set
Name | AVG(score) | Grade
Tim | 10 | High
Tom | 12 | High
Larry | 4 | Low
Kim | 6 | Medium

Hello @viciouscode. Yes you can! Consider the following code (assuming age is an integer value, and name is a TEXT value, and ToV is a valid table):

select age, name,
case
    when name+3 = 10 then "Is seven"
    else 'Not seven'
    end as "Are they seven"
from ToV;

If ToV contains three rows:

name|age
sam|7
ben|8
tod|11

When the above query is run, the following output will be observed:

age|name|Are they seven
7|sam|Is seven
8|ben|Not seven
11|tod|Not seven

I hope this helps!

Hi @codeneutrino! Thanks for the reply!

I was just wondering, since name is a TEXT value, then would the code actually fail since
TEXT + INT = INT does not seem right?

1 Like

Yes, sorry-If you did the whole thing but on the age column. Silly me!

select age, name,
case
    when age+3 = 10 then "Is seven"
    else 'Not seven'
    end as "Are they seven"
from ToV;

oh i see, @codeneutrino !

Is it possible to build another case statement on top of the “Are they seven” column to get the result set below?

Is seven = Yes
Not seven = No

age|name|Are they seven|Yes or No
7|sam|Is seven|Yes
8|ben|Not seven|No
11|tod|Not seven|No

1 Like

Hello @viciouscode, yes I believe it is (using the same example):

select age, name,
case
    when age+3 = 10 then "Is seven"
    else 'Not seven'
    end as "Are they 7",
case 
    when "Are they 7" = "Is seven" then "yes"
    else "no"
    end as "y/n"
from ToV;

Happy coding!

Thanks @codeneutrino! :smiley:

1 Like