x

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

Mark gravatar image

Mark
2.6k 23 25 27

@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
(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
GROUP BY ClientID
ORDER BY ClientID
more ▼

answered Jul 29, 2010 at 08:31 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

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
+1 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

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

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

SELECT ClientID, COUNT (DISTINCT(DepartmentID))
FROM #Testy
GROUP BY ClientID
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]
FROM
    #Testy
    GROUP BY [#Testy].[ClientID]

give this result

clientid    count of dep
1000        4
2000 4
more ▼

answered Jul 29, 2010 at 08:42 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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

binodbabu gravatar image

binodbabu
290 8 10 12

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1948
x292

asked: Jul 29, 2010 at 08:24 AM

Seen: 16570 times

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