Multiple COUNT(DISTINCT CAST(field AS DATE) in one query

user_15261234 0
Viewed 63

I need to find the count of distinct dates based on 2 criteria (as a whole), but I also need to show the distinct date counts for each of the 2 criteria's. Because the date field also includes a time, I am using:
COUNT(DISTINCT CAST(se.SeriesDate AS DATE)), which works just fine. But I'm struggling including the same count for each of the 2 criteria's in my WHERE clause.

I have tried:

SELECT ID,
COUNT(DISTINCT CAST(se.SeriesDate AS DATE)) VISITS,
COUNT(DISTINCT CAST(se.SeriesDate AS DATE) CASE WHEN se.Manufacture LIKE 'CIRRUS%' THEN se.SeriesDate END) Cirrus,
COUNT(DISTINCT CAST(se.SeriesDate AS DATE) CASE WHEN se.Manufacture LIKE 'Glaucoma%' THEN se.SeriesDate END) Glaucoma
From patient
WHERE (se.Manufacture LIKE 'CIRRUS%' OR se.Manufacture LIKE 'Glaucoma%'
GROUP BY ID

I would like it to return this (these are distinct visit dates):

Visits: 9
Cirrus: 6
Glaucoma: 3

This obviously doesn't work but after trying to research CAST and CASE together, I'm not finding anything. If this wasn't a date field, I understand how to do this. Has anyone done this before? TIA!

mysql
1 Answers

Conditional aggregation syntax:

SELECT ID,
COUNT(DISTINCT CAST(se.SeriesDate AS DATE)) VISITS,
COUNT(DISTINCT CASE WHEN se.Manufacture LIKE 'CIRRUS%' 
                    THEN CAST(se.SeriesDate AS DATE) END) Cirrus,
COUNT(DISTINCT CASE WHEN se.Manufacture LIKE 'Glaucoma%' 
                    THEN CAST(se.SeriesDate AS DATE) END) Glaucoma
From patient
WHERE (se.Manufacture LIKE 'CIRRUS%' OR se.Manufacture LIKE 'Glaucoma%')
GROUP BY ID;
user_50708790
Related