SQL Count Distinct with multiple Group By's

Posted on 2012-08-17
Medium Priority
Last Modified: 2012-08-17
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

Question by:fcsIT
1 Comment
LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 38307006
use DISTINCT in the count, e.g. count(DISTINCT cl.CASE_NUM)

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question