Simple CASE WHEN
Searched CASE WHEN
CASE WHEN with aggregates
CASE WHEN with GROUP BY
Instruction
Correct, nice! Now, let's use SUM in another way. Take a look:
SELECT
SUM(CASE WHEN pay_date BETWEEN '2015-06-01' AND
'2015-06-05' THEN 1 ELSE 0 END)
AS accepted_payment,
SUM(CASE WHEN pay_date = '2015-06-06' THEN 1
ELSE 0 END) AS conditionally_accepted_payment,
SUM(CASE WHEN pay_date > '2015-06-06'
THEN 1 ELSE 0 END) AS not_accepted
FROM application;
In the above query, we start with calculating the number of accepted payments.
Inside the function SUM, we use a CASE WHEN construction. It checks whether the pay_date in a given row is within the deadline specified. If it is, this row is given the number 1 (which means the sum count will go up by 1). If this is not the case, the number is 0 and this row will not affect the sum (e.g., 10 plus 0 is still 10).
The other two SUM functions (conditionally_accepted_payment, not_accepted) work in the same way.
Exercise
Count the number of applications that have:
- a full fee of 50 (
full_fee_sum). - a fee of 10 (
reduced_fee_sum). - a fee of 0 (
free_sum). - a fee of
NULL(null_fee_sum).
Stuck? Here's a hint!
Start with
SELECT SUM(CASE WHEN fee = 50 THEN 1 ELSE 0 END)and continue in the same way.
