Introduction
Simple CASE WHEN
Searched CASE WHEN
CASE WHEN with aggregates
16. CASE WHEN with COUNT
CASE WHEN with GROUP BY
Revision

Instruction

Okay. You can also use CASE WHEN with COUNT to count selected rows. The secret here is to omit the ELSE part. Let's try to rewrite the example with payment acceptance using COUNT:

SELECT
  COUNT(CASE WHEN pay_date
        BETWEEN '2015-06-01' AND '2015-06-05'
        THEN pay_date END) AS accepted_payment,

  COUNT(CASE WHEN pay_date = '2015-06-06' THEN
    pay_date END) AS conditionally_accepted_payment,

  COUNT(CASE WHEN pay_date > '2015-06-06' THEN
    pay_date END) AS not_accepted

FROM application;

Let's see what's changed. We don't use SUM, instead we use COUNT. The condition after WHEN stays the same, but now, if the condition is satisfied, we provide the column pay_date for that particular row so that COUNT can count it. The CASE WHEN does not have the ELSE part, so if the condition is not met, CASE WHEN returns NULL, which is not counted by COUNT.

In this way, we calculate accepted_payment, conditionally_accepted_payment and not_accepted.

Exercise

Count the number of courses with possible scholarship (scholarship_present) and without them (scholarship_missing).

Stuck? Here's a hint!

One of the counts should be:

COUNT(CASE WHEN scholarship IS TRUE THEN id END)

Console

Code editor

Result

TableConsole