Introduction
Simple CASE WHEN
Searched CASE WHEN
9. Searched CASE WHEN with various conditions
CASE WHEN with aggregates
CASE WHEN with GROUP BY
Revision

Instruction

Alright. As we mentioned previously, you can use various conditions in a single CASE WHEN clause. As usual, here comes an example:

SELECT candidate_id,
  CASE
    WHEN pay_date < '2015-06-01' THEN 'database error'
    WHEN pay_date BETWEEN '2015-06-01'
      AND '2015-06-05' THEN 'accepted'
    WHEN pay_date = '2015-06-06'
      THEN 'conditionally accepted'
    ELSE 'not accepted'
  END AS payment_status
FROM application;

The situation is as follows: each candidate was supposed to pay an application fee between 1 June and 5 June. If the system provides an earlier date, this must be clearly a mistake, because the bank account was only opened on 1 June (hence the status 'database error'). Those who indeed paid between 1 June and 5 June got a status 'accepted'. The university also agreed to accept those who paid a day later ('conditionally accepted'). There was, however, no mercy for those who paid even later – they did not qualify. Poor them.

Note that we could use the less than sign, the BETWEEN construction and the equality sign (=) - all in a single CASE clause. In other words, you can basically use all the operators and functions you've learned so far. The magic of searched CASE WHENs.

Exercise

For each candidate, show the first_name and last_name and a third column called overall_result: if the sum of score_math + score_language is 150 or more, show 'excellent'. If it's between 100 and 149, show 'good'. Otherwise, show 'poor'.

Stuck? Here's a hint!

Use

CASE WHEN score_math+score_language > 150 THEN 'excellent'
and so on.

Console

Code editor

Result

TableConsole