Summer Deals - 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!

Start with

SELECT SUM(CASE WHEN fee = 50 THEN 1 ELSE 0 END)
and continue in the same way.