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

Code editor

TableConsole