whytheq whytheq - 1 month ago 15
SQL Question

Partition Function COUNT() OVER possible using DISTINCT

I'm trying to write the following in order to get a running total of distinct NumUsers

NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth])


...management studio doesn't seem too happy. Take out
DISTINCT
and the error goes but then it won't be a distinct count.

Is
DISTINCT
not possible within the partition functions?
If so how do I go about finding the distinct count - use a more traditional method such as a correlated subquery?

Looking into this a bit further - Maybe these
OVER
fUnctions cannot be used in
SQL-Server
to calculate running totals; unlike Oracle.

I've added a live example here on SQLfiddle where I try to use a partition function to calculate a running total

Answer

There is a very simple solution using dense_rank()

dense_rank() over (partition by [Mth] order by [UserAccountKey]) 
+ dense_rank() over (partition by [Mth] order by [UserAccountKey] desc) 
- 1

This will give you exactly what you were asking for: The number of distinct UserAccountKeys within each month.