Partition Function COUNT() OVER possible using DISTINCT
sql, sql-server, sql-server-2008-r2, sql-server-2014, t-sql
Solution
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.
Problem
I'm trying to write the following in order to get a running total of distinct NumUsers, like so: ``` NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth]) ``` Management studio doesn't seem too happy about this. The error disappears when I remove the `DISTINCT` keyword, but then it won't be a distinct count. `DISTINCT` does not appear to be possible within the partition functions. How do I go about finding the distinct count? Do I use a more traditional method such as a correlated subquery? Looking into this a bit further, maybe these `OVER` functions work differently to Oracle in the way that they cannot be used in `SQL-Server` to calculate running totals. I've added a live example here on SQLfiddle where I attempt to use a partition function to calculate a running total.