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
19. CASE WHEN with GROUP BY
Summary

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 applications have 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. (Don't show the courses that aren't present in the application table.)

Sort the results by ID in 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