
Searched CASE WHEN
8. Searched CASE WHEN
CASE WHEN with aggregates


Good job! We told you previously that there is also another type of CASE WHEN which is searched CASE WHEN. This is a slightly more complicated form, but don't worry – it's no rocket science. Take a look at the example:

  WHEN score_math > 80 THEN 'amazing mathematician'
  WHEN score_math > 60 THEN 'knows something about numbers indeed'
  ELSE 'not good enough'
END AS mathematical_skills
FROM candidate;

Let's analyze what is happening here. First of all, there is now no column name immediately after the keyword CASE. Instead, we put entire logical conditions with column names after each WHEN. Again, you can have as many WHEN ... THEN statements as you want and you can skip ELSE if you like.

A simple CASE WHEN took a single column and checked it against a number of values we provided. A searched CASE WHEN is more general – you can put different column names in each WHEN clause and you can use mathematical and logical operators. We'll show you that in a second.

In this case, those candidates who had a score greater than 80 will have 'amazing mathematician' displayed, those with a score higher than 60 but lower than 80 will have 'knows something about numbers indeed' shown instead. Those who scored 60 or less will have 'not good enough' displayed.


Let's make a similar query for language skills. For each candidate, show their first and last name (first_name, last_name) along with a third column language_skills with the following possibilities:

  • 'amazing linguist' (>80 language points).
  • 'can speak a bit' (>50 language points).
  • 'cannot speak a word' otherwise.

Stuck? Here's a hint!

Use the following piece of code and continue in the same way:

  WHEN score_language > 80 THEN 'amazing linguist'