SQL Count Distinct with multiple Group By's

Posted on 2012-08-17
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

    use DISTINCT in the count, e.g. count(DISTINCT cl.CASE_NUM)

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now