Count Distinct with Group By

I thought I'd use you're expertise on this since I'm not doing well with it so far and I can't find this answer here with a search.

I want a count of unique departments, but grouped by Client. This works for a single client:

 SELECT      COUNT (D.DepartmentID) 
 FROM (Select Distinct DepartmentID FROM #BufferTemp1 where ClientID = 1234) D

But, I need to run this on a table with multple clients, so somehow need to group on clients.

 Create table #Testy (ClientID char(10), DepartmentID char(10))
 INSERT INTO #Testy VALUES ('1000', '0050')
 INSERT INTO #Testy VALUES ('1000', '0050')
 INSERT INTO #Testy VALUES ('1000', '0060')
 INSERT INTO #Testy VALUES ('1000', '0070')
 INSERT INTO #Testy VALUES ('1000', '0080')
 INSERT INTO #Testy VALUES ('2000', '0001')
 INSERT INTO #Testy VALUES ('2000', '0002')
 INSERT INTO #Testy VALUES ('2000', '0001')
 INSERT INTO #Testy VALUES ('2000', '0003')
 INSERT INTO #Testy VALUES ('2000', '0004')

The results should be: (edit: sorry - actually the results are much simpler than the original answer)

 ClientID    Department Count
 1000        4    
 2000        4

(By the way, I didn't see a tag for either COUNT or DISTINCT)

more ▼

asked Jul 29, 2010 at 08:24 AM in Default

avatar image

2.6k 24 27 31

@Mark Your original query for a single client is unreasonably expensive because it uses a subselect when it does not have to. You could restate it to:

select count(Distinct DepartmentID) DeptCount
    from #BufferTemp1 
    where ClientID = 1234;

This also means that the answer offered by Blackhawk-17 is perfect (the last statement in the answer).

Jul 29, 2010 at 08:47 AM Oleg

Oleg, Yes, I was frustrtated trying to get COUNT and DISTINCT to work together, so I started resorting to that convoluted method.

Jul 29, 2010 at 08:55 AM Mark

[meta] Can we add tags for COUNT and DISTINCT? Or are they too specific?

Jul 29, 2010 at 08:58 AM Mark

@Mark This happened to me in the past too when I needed to figure out how to use count and distinct together for the first time. The trouble is that distinct (without count) exists exclusively for wimps, there is no distinct (without count) in T-SQL. What I mean is:

select distinct some_column
    from some_table;

is inevitably getting translated by the parser to a more proper

select some_column
    from some_table
    group by some_column;

Where distinct actually shines is as an optional parameter to the count. The actual definition of count is:

count ({[[ all | distinct ] expression] | * })

As you can see, if none of the 2 is specified, all is assumed (spelled out for you by the parser). This is what makes the proper usage unclear. If we always had to spell out one of the 2 words then we would never be unclear how to use it:

select count(all some_column) from some_table;

instead of simply

select count(some_column) from some_table;

The former would immediately hint us how to use distinct and count :)

Jul 29, 2010 at 09:09 AM Oleg

I'm used to using DISTINCT by itself rather than in a function format with () involved (e.g., SELECT DISTINCT ClientID...).

Jul 29, 2010 at 09:14 AM Mark
show all comments (comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
 SELECT ClientID, DepartmentID, COUNT(DepartmentID) AS [Count]
 FROM #Testy
 GROUP BY ClientID, DepartmentID
 ORDER BY ClientID, DepartmentID  

With your new result requirements maybe this is it:

 SELECT ClientID, COUNT(DISTINCT(DepartmentID)) AS [Department Count]
 FROM #Testy
more ▼

answered Jul 29, 2010 at 08:31 AM

avatar image

12.1k 30 36 42

Blackhawk - thanks and my comment to Thomas applies to you too). This looks right - let me test it some... I couldn't get COUNT and DISTINCT to work together this way for some reason.

Jul 29, 2010 at 08:40 AM Mark

Yes, you have it right Blackhawk and such a simple solution - I'm embarrased now darn it! Oh well, I'll know from now on. COUNT will play well with DISTINCT if you can get the syntax right.

Jul 29, 2010 at 08:44 AM Mark

Yup... SQL. It takes 2 seconds to learn it - a lifetime to master it.

Jul 29, 2010 at 08:46 AM Blackhawk-17
  • The last statement in the answer is perfect. From the question definition, it looks like this is exactly what Mark needs.

Jul 29, 2010 at 08:49 AM Oleg
(comments are locked)
10|1200 characters needed characters left

You can run aggregates against multiple columns. Following your example:

 SELECT ClientID, DepartmentID, COUNT(*)
 FROM #Testy
 GROUP BY ClientID, DepartmentID
more ▼

answered Jul 29, 2010 at 08:28 AM

avatar image

ThomasRushton ♦♦
41.8k 20 50 53

Sorry Thomas, my ansewer was not correct at first. I just want a count (by client) of distinct departments. (+1 anyway - you're right)

Jul 29, 2010 at 08:35 AM Mark

In that case

 FROM #Testy

and Blackhawk's beaten me to it.

Jul 29, 2010 at 08:40 AM ThomasRushton ♦♦

Back and forth we go...

Jul 29, 2010 at 08:41 AM Blackhawk-17

I've tested mine now. Both work. ;-)

Jul 29, 2010 at 08:42 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

I think this is what you are looking for

 SELECT clientid,
    count(DISTINCT departmentid) AS [count of dep]
     GROUP BY [#Testy].[ClientID]

give this result

 clientid    count of dep
 1000          4
 2000          4
more ▼

answered Jul 29, 2010 at 08:42 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

Thanks Fatherjack, +1 for you too.

Jul 29, 2010 at 08:51 AM Mark
(comments are locked)
10|1200 characters needed characters left

Though Jonathan Allen script is better than this but we can do this way also right?

how about this

with abc(clientID,DepartmentID,countdepartmentID) as ( select clientID,DepartmentID,COUNT(departmentID) from #testy group by clientID,DepartmentID) select clientid,COUNT(departmentID) from abc group by ClientID

more ▼

answered Mar 01, 2012 at 08:36 PM

avatar image

290 10 10 15

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 29, 2010 at 08:24 AM

Seen: 28763 times

Last Updated: Mar 01, 2012 at 08:37 PM

Copyright 2017 Redgate Software. Privacy Policy