I have a table of Visits and wanted to return a subset of all visits in the 
table along with the distinct count of clients per employee within that 
subset of rows.  I can do it with a subquery or maybe a CTE, but the actual 
(production) query has a lot of parameters so it really complicates it.  I 
was hoping to use the OVER PARTITION for this, but it errors when I use the 
DISTINCT on the COUNT.  I don't see anything in BOL mentioned about this not 
being supported.  Am I doing something wrong or is this simply something not 
supported?

Using SQL 2008 SP1
CREATE TABLE Visit (VisitID Int, ClientID Int, EmployeeID Int)

INSERT Visit VALUES (1,2,50),(2,3,50),(3,3,50),(4,2,60),(5,3,60),(6,4,60)

-- This errors:

SELECT VisitID, EmployeeID, ClientID,

       COUNT(DISTINCT ClientID) OVER(PARTITION BY EmployeeID) AS 
'DistinctClientCount'

  FROM Visit

It's not supported at this moment but if you want it on a future version of 
SQL-Server, you can vote for it on Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"David W" <spivey@nospam.post.com> wrote in message 
news:ewkh7RxjKHA.4672@TK2MSFTNGP06.phx.gbl...
>I have a table of Visits and wanted to return a subset of all visits in the 
>table along with the distinct count of clients per employee within that 
>subset of rows.  I can do it with a subquery or maybe a CTE, but the actual 
>(production) query has a lot of parameters so it really complicates it.  I 
>was hoping to use the OVER PARTITION for this, but it errors when I use the 
>DISTINCT on the COUNT.  I don't see anything in BOL mentioned about this 
>not being supported.  Am I doing something wrong or is this simply 
>something not supported?
>
> Using SQL 2008 SP1
> CREATE TABLE Visit (VisitID Int, ClientID Int, EmployeeID Int)
>
> INSERT Visit VALUES (1,2,50),(2,3,50),(3,3,50),(4,2,60),(5,3,60),(6,4,60)
>
> -- This errors:
>
> SELECT VisitID, EmployeeID, ClientID,
>
>       COUNT(DISTINCT ClientID) OVER(PARTITION BY EmployeeID) AS 
> 'DistinctClientCount'
>
>  FROM Visit
>
> 

As already noted currently this is not supported. Here is a workaround:

SELECT VisitID, EmployeeID, ClientID,
        MAX(rk) OVER(PARTITION BY EmployeeID) AS distinct_client_count
FROM (
SELECT VisitID, EmployeeID, ClientID,
        DENSE_RANK() OVER(PARTITION BY EmployeeID ORDER BY ClientID) AS rk
FROM Visit) AS V;

-- 
Plamen Ratchev
http://www.SQLStudio.com

Thanks, I have added my vote. 

Thanks.  Very interesting and creative.