Introduction
Simple CASE WHEN
Searched CASE WHEN
8. Searched CASE WHEN
CASE WHEN with aggregates
CASE WHEN with GROUP BY
Revision

Instruction

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:

SELECT CASE
  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 happened 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' printed, 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 be presented with 'not good enough'.

Exercise

Let's make a similar query for language skills. For each candidate, show their first and last name along with the 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:

CASE WHEN score_language > 80 THEN 'amazing linguist'
and continue in the same way.

Console

Code editor

Result

TableConsole