Good! Now, in certain situations you may also want to include DISTINCT
in your CASE WHEN
statements with COUNT
. Take a look:
SELECT
COUNT(DISTINCT CASE
WHEN pay_date BETWEEN '2015-06-01' AND '2015-06-05'
THEN candidate_id
END) AS accepted_student,
COUNT(DISTINCT CASE
WHEN pay_date = '2015-06-06'
THEN candidate_id
END) AS conditionally_accepted_student,
COUNT(DISTINCT CASE
WHEN pay_date > '2015-06-06'
THEN candidate_id
END) AS not_accepted
FROM application;
What changed here? Two things. First of all, we included the keyword DISTINCT
in each COUNT
. Second, we now count student_id
s and not pay_date
s. What is the meaning of these changes?
We previously counted the number of accepted payments, conditionally accepted payments and not accepted payments. Now, we count the number of candidates who were accepted for at least one degree course, the number of candidates who were conditionally accepted for at least one degree course and not accepted for at least one degree course.
We had to include the keyword DISTINCT
because a single candidate can apply for more than one degree course, so if a candidate made two payments for two courses on time, that candidate would be counted twice (the candidate can be still counted twice in two different columns if the candidate paid for one degree course and didn't pay for another, but that's a different story).