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

Instruction

Very good. Now, let's move on and analyze how we can use CASE WHEN and GROUP BY together. Take a look at the following example:

 

SELECT 
  course_id,
  SUM(CASE WHEN fee = 50 THEN 1 ELSE 0 END) AS full_fee,
  SUM(CASE WHEN fee = 10 THEN 1 ELSE 0 END) AS reduced_fee,
  SUM(CASE WHEN fee = 0 THEN 1 ELSE 0 END) AS no_fee
FROM application
GROUP BY course_id;

 

Look what happened in the above query: we count the number of applications with full fee, reduced fee and no fee for single courses. In other words, we know how many people paid a full fee for Baking Technology, Viking Studies etc. We use SUMs as we did before, but now we also group the rows so that these sums refer to specific groups.

Exercise

For each course, show its id (name the column course_id) and three more columns: accepted, pending and rejected, each containing the number of accepted, pending and rejected applications for that course, respectively.

Sort the results by id in the ASCending order.

Stuck? Here's a hint!

The first part of CASE WHEN will look like this:

SUM(CASE WHEN status = 'accepted' THEN 1 ELSE 0 END) AS accepted

 

Console

Code editor

Result

TableConsole