Partition Function COUNT() OVER possible using DISTINCT

Question

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.

Answer 1

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.

Answer 2

Necromancing:

It's relativiely simple to emulate a COUNT DISTINCT over PARTITION BY with MAX via DENSE_RANK:

;WITH baseTable AS (     SELECT 'RM1' AS RM, 'ADR1' AS ADR     UNION ALL SELECT 'RM1' AS RM, 'ADR1' AS ADR     UNION ALL SELECT 'RM2' AS RM, 'ADR1' AS ADR     UNION ALL SELECT 'RM2' AS RM, 'ADR2' AS ADR     UNION ALL SELECT 'RM2' AS RM, 'ADR2' AS ADR     UNION ALL SELECT 'RM2' AS RM, 'ADR3' AS ADR     UNION ALL SELECT 'RM3' AS RM, 'ADR1' AS ADR     UNION ALL SELECT 'RM2' AS RM, 'ADR1' AS ADR     UNION ALL SELECT 'RM3' AS RM, 'ADR1' AS ADR     UNION ALL SELECT 'RM3' AS RM, 'ADR2' AS ADR ) ,CTE AS (     SELECT RM, ADR, DENSE_RANK() OVER(PARTITION BY RM ORDER BY ADR) AS dr      FROM baseTable ) SELECT      RM     ,ADR      ,COUNT(CTE.ADR) OVER (PARTITION BY CTE.RM ORDER BY ADR) AS cnt1      ,COUNT(CTE.ADR) OVER (PARTITION BY CTE.RM) AS cnt2      -- Not supported     --,COUNT(DISTINCT CTE.ADR) OVER (PARTITION BY CTE.RM ORDER BY CTE.ADR) AS cntDist     ,MAX(CTE.dr) OVER (PARTITION BY CTE.RM ORDER BY CTE.RM) AS cntDistEmu  FROM CTE 

Note:
This assumes the fields in question are NON-nullable fields.
If there is one or more NULL-entries in the fields, you need to subtract 1.