DISTINCT COUNT in SELECT CASE SQL

Question

I have a table of reports that include the fields Case (unique number), ISR (Individual Safety Report - unique number) and YearsOld.

There can be more than one ISR for each Case. I want to count the number of unique Cases within age groups.

This SQL gives me a count of the number of ISRs:

SELECT  
COUNT(CASE WHEN `YearsOld` = -2) THEN 1 END) `No Report`,
COUNT(CASE WHEN `YearsOld` BETWEEN 0 AND 5) THEN 1 END) `0 to 5`
COUNT(CASE WHEN `YearsOld` BETWEEN 6 AND 12) THEN 1 END) `6 to 12`
FROM `Demographics`

is there a way to modify this to count the DISTINCT Cases for these Age Groups?

Answer 1

If your "case" variable is unique, you can certainly put the distinct keyword in the SQL CASE syntax directly:

Count(distinct CASE when yearsold between 6 and 12 then case else null end)

That way, each unique value of the case variable is counted only once.

Just a note on column naming, I would suggest not using a word that has meaning in SQL if you have a choice (I.e. use 'case_num' instead of case).

Answer 2

You could use a subquery to filter your demographics table for a single YearsOld field per case, although if that case might have been related to difference ages for different ISR it'll only end up being counted in one bracket (perhaps this is what you want?):

SELECT
  ... -- as you currently have
FROM (
  SELECT `Case`, `YearsOld` from `Demographics` GROUP BY `Case`
) t;

Alternatively, to "count" each "distinct" "case" within each bracket, you do literally that:

SELECT
  COUNT(DISTINCT CASE WHEN `YearsOld` = -2 THEN 1 END) `No Report`,
  COUNT(DISTINCT CASE WHEN `YearsOld` BETWEEN 0 AND  5 THEN `Case` END) `0 to 5`,
  COUNT(DISTINCT CASE WHEN `YearsOld` BETWEEN 6 AND 12 THEN `Case` END) `6 to 12`
FROM Demographics;