# 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!

1 Like