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
16. CASE WHEN with COUNT
CASE WHEN with GROUP BY
Summary

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)