Introduction
Simple CASE WHEN
Searched CASE WHEN
CASE WHEN with aggregates
CASE WHEN with GROUP BY
22. Another use of CASE WHEN with GROUP BY
Revision

Instruction

Alright. We will now show you two other ways of using CASE WHEN with GROUP BY statements and aggregate functions. Take a look at the following example:

SELECT
  pay_date,
  COUNT(pay_date),

  CASE WHEN COUNT(pay_date) > 5
  THEN 'high' ELSE 'low' END
  AS number_of_payments

FROM application
GROUP BY pay_date;

The above query will group all applications according to the date where the payment was made. Then, it counts the number of payments on each date. Up to this point, it's pretty standard, right? But look what happens next: we can use CASE WHEN to show a comment on the values of the aggregate function. If it's more than 5, we'll say that the number is 'high'. Otherwise, we'll show 'low'.

Exercise

Show each preferred_contact form with the number of candidates who provided it. Apart from that, show a column entitled 'rating' and produce:

  • 'high' if there are more than 5 candidates with this form of contact,
  • or 'low' otherwise.

Stuck? Here's a hint!

Use COUNT(id) in the CASE WHEN part and GROUP BY preferred_contact;

Console

Code editor

Result

TableConsole