Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Standard SQL Functions

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
Summary

Instruction

All right. 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 (as candidates_count). Along with that, show a column entitled 'rating' and show:

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

Stuck? Here's a hint!

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