TSQL - Incremental Total going down my table

Question

I would like to have a rolling total in a field in my table. Example:

ID - Name - Total - RollingTotal
1 - James - 10 - 10
2 - John - 20 - 30
3 - Matthew - 10 - 40

Can anybody explain how to do this in SQL ? I literally have no idea how to go about this and it's confusing me!

I have asked the wrong question. My table actually looks more like this

ID - Group - Name - Total - Rolling Total
1 - 1 - James - 10 - 10
2 - 1 - John- 10 - 20
3 - 2 - Matthew - 20 - 20
4 - 2 - Stephanie - 30 - 50

What is the stackOverflow etiquette here. The orginal question has been answered, but I didn't explain myself well enough. Sorry all.

http://www.eggheadcafe.com/microsoft/SQL-Server-Programming/33868189/incremental-sum-in-tsql.aspx — Tim Schmelter, Nov 16 '11 at 10:57
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Martin Smith, Nov 16 '11 at 11:00
@Waller, do you want the rolling total to be a field in your table (as requested in the question) or to be returned in a query (as provided in the answers so far)? — , Nov 16 '11 at 11:21
Mark Bannister. I am no expert in SQL and I am writing this to create a report of all our data for revenue management guys. The purpose of this is finding our incremental revenue. I would idealy like these results inside of the table (A select into statement)... Does this answer your quesiton? — Waller, Nov 16 '11 at 12:47
@Waller, following the amended requirements, see my comment on Marco's answer. — , Nov 16 '11 at 16:56

Marco · Accepted Answer · 2011-11-17T05:29:10.757

3

You could try:

SELECT t1.ID, t1.Name, t1.Total,
    (SELECT SUM(Total) FROM your_table t2
     WHERE t2.ID <= t1.ID) AS RollingTotal
FROM your_table t1
ORDER BY ID

EDITED after user new requirements:

SELECT t1.ID, t1.`group`, t1.Name, t1.Total,
    (SELECT SUM(Total) FROM your_table t2
     WHERE t2.ID <= t1.ID
       AND t2.`group` = t1.`group`) AS RollingTotal
FROM your_table t1
ORDER BY ID

edited Nov 17 '11 at 05:29

answered Nov 16 '11 at 10:56

Marco

56,740
14
129
152

For a 10,000 row table this will do 50,005,000 aggregations. – Martin Smith Nov 16 '11 at 11:05
@MartinSmith: yes, you're absolutely right, I know my query is not performant... but it's really the only way I know. There are others better for sure, but believe me, I don't know them. This is the only contribute I can give OP... – Marco Nov 16 '11 at 11:07
Add `t1.group,` to the main select statement, and modify the where clause in the subquery to be `WHERE t2.ID <= t1.ID and t1.group = t2.group`, to fulfill the amended question requirements. – Nov 16 '11 at 16:54

score 0 · Answer 2 · answered Nov 16 '11 at 11:02

I think your best option is to calculate the Rolling Total dynamically in a view, as @Marco suggests.

In most cases, it would not make a lot of sense to put a field like that directly in the table, because the rolling total associated with each row would depend on the order of insertion of rows and on the fact that no rows are then updated or deleted (which would obviously influence the rolling total for the rows following them).

If that is what you really need (i.e. the rows in your table have a "natural" order and they don't get updated/deleted after their first insertion), you could use triggers to calculate your Rolling Total on insert. The syntax depends on the RDBMS you're using.

TSQL - Incremental Total going down my table

2 Answers2