T-SQL using SUM for a running total

Question

I have a simple table with some dummy data setup like:

|id|user|value| --------------- 1 John 2 2 Ted 1 3 John 4 4 Ted 2

I can select a running total by executing the following sql(MSSQL 2008) statement:

SELECT a.id, a.user, a.value, SUM(b.value) AS total FROM table a INNER JOIN table b ON a.id >= b.id AND a.user = b.user GROUP BY a.id, a.user, a.value ORDER BY a.id

This will give me results like:

|id|user|value|total| --------------------- 1 John 2 2 3 John 4 6 2 Ted 1 1 4 Ted 2 3

Now is it possible to only retrieve the most recent rows for each user? So the result would be:

|id|user|value|total| --------------------- 3 John 4 6 4 Ted 2 3

Am I going about this the right way? any suggestions or a new path to follow would be great!

Are you only interested in the final result, or do you need the running total as well? Are you storing that running total somewhere already, or are you using it Only to generate your final result? [Your final result can be more effectively created without using a running total. But, if you already have the running total stored somewhere, for other reasons, it can certainly be used.] — MatBailie
– MatBailie, Commented Aug 16, 2012 at 7:14
You swithced your accepted answer to improve performance, but you haven't responded to my query above. Do note that you can improve performance by not using the running total method. Would that be of interest to you, or do you need all the results in the running total as well? — MatBailie
– MatBailie, Commented Aug 16, 2012 at 9:14
Hi Dems, I was busily working away and missed your comment. I'm only after the total and the associated user, so yes I am interested in improved performance. — luke2012
– luke2012, Commented Aug 16, 2012 at 9:47

t-clausen.dk · Accepted Answer · 2012-08-16 08:51:24Z

No join is needed, you can speed up the query this way:

select id, [user], value, total from ( select id, [user], value, row_number() over (partition by [user] order by id desc) rn, sum(value) over (partition by [user]) total from users ) a where rn = 1

this can be speeded up further by explicitly stating the additional arguments to the over clause of SUM OVER

Joe G Joseph · Accepted Answer · 2012-08-16 07:19:24Z

try this:

;with cte as (SELECT a.id, a.[user], a.value, SUM(b.value) AS total FROM users a INNER JOIN users b ON a.id >= b.id AND a.[user] = b.[user] GROUP BY a.id, a.[user], a.value ), cte1 as (select *,ROW_NUMBER() over (partition by [user] order by total desc) as row_num from cte) select id,[user],value,total from cte1 where row_num=1

SQL Fiddle Demo

@Joe G Joseph, it looks like the link to the SQL Fiddle is broken.

valex · Accepted Answer · 2012-08-16 06:59:17Z

add where statement:

select * from ( your select statement ) t where t.id in (select max(id) from table group by user)

also you can use this query:

SELECT a.id, a.user, a.value, (select max(b.value) from table b where b.user=a.user) AS total FROM table a where a.id in (select max(id) from table group by user) ORDER BY a.id

Michal Klouda · Accepted Answer · 2012-08-16 07:03:10Z

0

Adding a right join would perform better than nested select.

Or even simpler:

SELECT MAX(id), [user], MAX(value), SUM(value) FROM table GROUP BY [user]

edited Aug 16, 2012 at 7:03

answered Aug 16, 2012 at 6:55

Michal Klouda

14.6k7 gold badges58 silver badges79 bronze badges

3 Comments

valex Over a year ago

But max(id) and max(value) can be from different original table rows.

MatBailie Over a year ago

-1 : This is an oversimplified approach that simply does not work correctly. You can't assume that MAX(id) and MAX(value) give results from the same input row.

Michal Klouda Over a year ago

You are right, I realized after posting. But still, a smart right join would perform better than nested select.

Community · Accepted Answer · 2016-12-12 16:17:18Z

Compatible with SQL Server 2008 or later

DECLARE @AnotherTbl TABLE ( id INT , somedate DATE , somevalue DECIMAL(18, 4) , runningtotal DECIMAL(18, 4) ) INSERT INTO @AnotherTbl ( id , somedate , somevalue , runningtotal ) SELECT LEDGER_ID , LL.LEDGER_DocDate , LL.LEDGER_Amount , NULL FROM ACC_Ledger LL ORDER BY LL.LEDGER_DocDate DECLARE @RunningTotal DECIMAL(18, 4) SET @RunningTotal = 0 UPDATE @AnotherTbl SET @RunningTotal=runningtotal = @RunningTotal + somevalue FROM @AnotherTbl SELECT * FROM @AnotherTbl

Collectives™ on Stack Overflow

T-SQL using SUM for a running total

5 Answers 5

1 Comment

1 Comment

Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

3 Comments

Comments

Linked

Related