sql running total

Question

Im trying to generate a running total by month and year. Ive tried a few examples but I cant get it working. This is the sql I have and I would want to create a running total for the totalclients column

Month| Year| TotalClients| Running Total

Jan |2014| 1| 1

Feb| 2014| 4| 5

Mar| 2014| 8| 13


select Month, Year, TotalClients
From Total

This was the code I was trying to use, ive used a declare table as the main data comes from a different query but this should be the bit you need. I also commented out one of the from lines as I was trying out both way, the commented out line was in a few examples on the net but I couldn't get it working

select t1.monthstart, t1.yearstart, t1.TotalClients, sum(t2.TotalClients) as 'RunningTotal'
from @Totals t1 inner join  @Totals t2 on t1.monthstart = t2.monthstart and t1.yearstart = t2.yearstart
--from @Totals t1, @Totals t2 
WHERE t1.MonthStart <= t2.MonthStart and  t1.Yearstart <= t2.Yearstart
GROUP BY t1.Yearstart,  t1.MonthStart, t1.TotalClients
ORDER BY t1.yearstart , t1.monthstart

What version of SQL Server? Also, you state that you tried a few examples, you should include that code. It could be a simple fix to your current code. — Taryn, Feb 03 '15 at 14:47
Possibly using a windowed set using something like `Count(ClientID) over ( partition by (Year + Month)` Here's other options http://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals — xQbert, Feb 03 '15 at 14:53
@xQbert partition by year + month is normally not a good idea. Try partition by year, month to avoid 2014 + 01 = 2015 — t-clausen.dk, Feb 03 '15 at 15:07

score 1 · Answer 1 · answered Feb 03 '15 at 15:04

1

As @xQbert posted in comments above (I advise reading that article), SQL Server "Windowing Functions" is what you want to use in version 2012+. Windowing functions are flexible and powerful, and far more efficient than self-joins.

As an actual answer, here would be some possible code for you to use:

SELECT YearStart, MonthStart, 
    ClientCount = SUM(TotalClients) OVER (
        PARTITION BY YearStart, MonthStart 
        ORDER BY YearStart, MonthStart RANGE UNBOUNDED PRECEDING
    )
FROM Totals t1
ORDER BY YearStart, MonthStart

answered Feb 03 '15 at 15:04

SlimsGhost

2,849
1
10
16

I did try this example thanks, it gave me a running total for the month and year but didn't follow on to the next month, year combo although I think maybe splitting the data on month and year caused issues so I went back to using the full date in the end so this would probably work for that I imagine. – user3691566 Feb 03 '15 at 15:36

Code Different · Answer 2 · 2015-02-03T15:17:27.430

0

This query works for SQL Server 2012 and up. I assumed Month is numeric (Jan = 1, Feb = 2, etc.)

SELECT  *,
        SUM(t.TotalClients) OVER (PARTITION BY t.[Year] ORDER BY t.[Month])
FROM    @Totals t

It will reset the client count once the year changes. To keep it going, change the SUM clause to

SUM(t.TotalClients) OVER (ORDER BY t.[Year], t.[Month])

edited Feb 03 '15 at 15:17

answered Feb 03 '15 at 15:03

Code Different

90,614
16
144
163

gives me error "Incorrect syntax near 'order'". my version Microsoft SQL Server 2008 R2 (SP2) – Codeek Feb 03 '15 at 15:15
My faulty memory! Window functions have changed a lot in recent versions of SQL Server so it's hard to keep track of what version supports what. Answer updated. – Code Different Feb 03 '15 at 15:17

score 0 · Answer 3 · answered Feb 03 '15 at 15:15

0

I used this in the end, I added a faulldate in to simplify what I wanted and it worked, I think the issue was in the join I used it had the <= the wrong way around.

SELECT
    st1.invoicestartdate,
    st1.TotalClients,
    RunningTotal = SUM(st2.TotalClients)
FROM
    @Totals AS st1
INNER JOIN
    @Totals AS st2
    ON st2.invoicestartdate <= st1.invoicestartdate
GROUP BY st1.invoicestartdate, st1.TotalClients
ORDER BY st1.invoicestartdate;

answered Feb 03 '15 at 15:15

user3691566

303
2
8
17

1

was about to point that out, bt saw everyone suggesting window function so thought of trying that first – Codeek Feb 03 '15 at 15:16

sql running total

3 Answers3

Linked