Count Distinct with Group By

@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).

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

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

@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 :)

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