whytheq whytheq - 1 year ago 76
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
and the error goes but then it won't be a distinct count.

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
fUnctions cannot be used in
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download