Sql running totals but from a sum of a count, grouped.

darkgen · 7 Mar 2015 at 09:03

So the following code produces a count of unique users grouped by week.

Code:

SELECT        TOP (100) PERCENT DATEPART(week, time_stamp) AS Week, COUNT(DISTINCT [user]) AS UnqCnt
FROM            dbo.vw_ctrx_archive_ica_roundtrip_perf
WHERE        (time_stamp BETWEEN GETDATE() - 120 AND GETDATE()) AND (dept_path LIKE 'All\XenApp Farms\AppName\%')
GROUP BY DATEPART(week, time_stamp)
ORDER BY week

What I need to do is then from this data produce a running total of unique users to date, split by week. So in essence in week 1 there were 100 unique users, by the end of week 2 there were 200 unique users and so forth. I know I need to subquery the lot but since I'm am counting a distinct alphanumeric field ([user]) as opposed to summing a numeric I'm a little confuddled how to do this.

Pointers much appreciated.

Cheers, Paul.

mid_gen · 7 Mar 2015 at 11:55

If you're on Oracle you can use the analytic functions (OVER PARTITION BY syntax) for running totals.

There's an ask tom with example code :

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1793764100346222947

darkgen · 7 Mar 2015 at 13:42

mid_gen said:
If you're on Oracle you can use the analytic functions (OVER PARTITION BY syntax) for running totals.

There's an ask tom with example code :

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1793764100346222947

Using MSSQL in this instance.

Dolph · 7 Mar 2015 at 16:53

darkgen said:
Using MSSQL in this instance.

If you are using SQL server 2012, you can do sum(column) over (order by etc)

If you are using an earlier version of SQL server, best solution involves a cursor, but there are numerous approaches here.

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

darkgen · 8 Mar 2015 at 07:22

Thanks; Actually its SQL2008R2 in this instance, ugh cursor by the looks of things. I'll go have another look!

So the immediate choice is the inefficient cheat via ;

Code:

SELECT        TOP (100) PERCENT Week, UnqCnt,
                             (SELECT        SUM(UnqCnt) AS Expr1
                               FROM            dbo.vw_someview AS t2
                               WHERE        (Week <= t1.Week)) AS RunningT
FROM            dbo.vw_someview AS t1
ORDER BY Week

This doesn't actually provide what I need anyway since its simply SUMS the output from the OP, which returns a sum of unique users in each week and not to date, which is what I require.Again its the COUNT aspect and not working directly with numerics that is throwing me.

Thanks, Paul.