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]
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