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)



