Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Simple CASE WHEN
Searched CASE WHEN
CASE WHEN with aggregates
14. Counting rows with CASE WHEN and SUM
CASE WHEN with GROUP BY
Summary

## 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!

SELECT SUM(CASE WHEN fee = 50 THEN 1 ELSE 0 END)