CODEWITHSUNDEEP
sqlsql-server
Brice Walker
Brice Walker

Reputation: 19

Runnning total of unique string values in column over days

I have an sql table of device logins that looks like:

date,        username,      devicename
2018-09-05,  bricew,       brice-pc
2018-09-05,  bricew,       brice-laptop
2018-09-06,  bricew,       brice-laptop
2018-09-06,  bricew,       brice-ipad
2018-09-06,  johnm,        john-pc
2018-09-06,  bricew,       brice-laptop

How can I get a running total of unique devices per username per day? I want a column that would look like this for the example:

running_total
1
2
2
3
1
3

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

SQL Server has window functions. So, it would seem that you could do:

select t.*,
       count(distinct devicename) over (partition by date, username) as running_total
from t;

Alas, support for window functions does not extend to this use of count(distinct). You can do this, using a subquery. One method uses row_number():

select t.*,
       sum(case when seqnum = 1 then 1 else 0 end) over (partition by date, username) as running_total
from (select t.*,
             row_number() over (partition by date, username, device_name order by (select null)) as seqnum
      from t
     ) t;

Another method uses dense_rank():

select t.*,
       max(seqnum) over (partition by date, username) as running_total
from (select t.*,
             dense_rank() over (partition by date, username order by device_name) as seqnum
      from t
     ) t;

Upvotes: 2

Related Questions