Introduction
Simple CASE WHEN
Searched CASE WHEN
CASE WHEN with aggregates
14. Counting rows with CASE WHEN and SUM
CASE WHEN with GROUP BY
Revision

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 (conditionally_accepted_payment, not_accepted) work in the same way.

Exercise

Count the number of candidates who:

  • paid a full fee of 50 (full_fee_sum),
  • a fee of 10 (reduced_fee_sum),
  • a fee of 0 (free_sum),
  • the number of candidates with NULL fee (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.

Console

Code editor

Result

TableConsole