Good! Now, in certain situations you may also want to include
DISTINCT in your
CASE WHEN statements with
COUNT. Take a look:
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)
COUNT(DISTINCT CASE WHEN pay_date > '2015-06-06'
THEN candidate_id END) AS not_accepted
What changed here? Two things. First of all, we included the keyword
DISTINCT in each
COUNT. Second of all, we now count
student_ids and not
pay_dates. 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).