SQL Count Distinct with multiple Group By's

I have the code shown below.  It's currently giving me the counts of all instances of cl.CASE_NUM.  I need it to give me the count of distinct cl.CASE_NUM records for each svc.SUB_UNIT_ID.  Any ideas on how I can accomplish that?  (Yes, I've tried putting distinct in front of it.  That gives me the exact same result.)

select count(cl.CASE_NUM), svc.SUB_UNIT_ID, unit.[DESC]
from svc
	inner join cl on cl.ID = svc.CLIENT_ID
	inner join unit on unit.ID = svc.SUB_UNIT_ID
where svc.BEG_DATE >= '2011-07-01'
and svc.BEG_DATE <= '2012-06-30'
and svc.SVC_ID < 900
and unit.ACTIVE_FLAG = 'Y'
and cl <> 0
group by svc.SUB_UNIT_ID, unit.[DESC]
order by svc.SUB_UNIT_ID

Open in new window

Who is Participating?
use DISTINCT in the count, e.g. count(DISTINCT cl.CASE_NUM)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.