whytheq - 7 months ago 41

SQL Question

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`

Is

`DISTINCT`

If so how do I go about finding the distinct count - use a more

Looking into this a bit further - Maybe these

`OVER`

`SQL-Server`

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.