COUNT(DISTINCT) OVER() aggregate window function

Recently have been using some of the new partition and aggregate window functions in SQL Server 2005.(see here for the BOL entry).

However when I tried to change a count(distinct ...) query, I ran into an error!

create table TableA(
id int,
category varchar(10),
subcategory varchar(10),
sometext varchar(100)
)
go
insert into TableA (id, category, subcategory, sometext)
select 1,'Category A','fruit','apple'
union select 2,'Category A','fruit','banana'
union select 3,'Category A','fruit','cherry'
union select 4,'Category A','animal','cat'
union select 5,'Category A','animal','dog'
union select 6,'Category A','animal','lion'
union select 7,'Category B','vegetable','carrot'
union select 8,'Category B','vegetable','potato'
union select 9,'Category B','vegetable','onion'
union select 10,'Category B','name','alice'
union select 11,'Category B','name','bob'
union select 12,'Category B','place','london'
union select 13,'Category B','place','manchester'
go


from this sample data we can easily count the number of subcategories per category using


select category, count(distinct subcategory)
from TableA
group by category

which gives

category CountSubcategory
---------- ----------------
Category A 2
Category B 3

(2 row(s) affected)

but if we try to use the OVER() clause

select category, count(distinct subcategory) over(partition by category)
from tableA

we get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.


Check BOL, and there's nothing that says that the DISTINCT argument cannot be used in the COUNT function when used in an aggregate window function. How confusing. Then I discover I'm not the only one to have the same thought, as this item on Connect shows.





So if you have the same issue and want this resolved, please log on to Connect and vote this up.