How to calculate "running total" in SQL

Question

Given a table

ID FRUIT
-- -----
1  APPLE
2  BANANA
3  PEAR
4  APPLE
5  APPLE
6  PEAR

I want to get this

ID FRUIT RUNNING_TOTAL
-- ----- -------------
1  APPLE     1
2  BANANA    1
3  PEAR      1
4  APPLE     2
5  APPLE     3
6  PEAR      2

(going in the ID order, the first time we encounter a given fruit we set the value of the RUNNIN_TOTAL for that row to 1; the second time we encounter a given fruit, RUNNIN_TOTAL is 2, and so on).

I think I need to first add a column like this:

alter table Fruits add RUNNING_TOTAL int null

Then set the values for the new column with some thing like this

update Fruits set RUNNING_TOTAL = ...

but I am not sure how to complete the last statement. Can someone help? I am using SQL SERVER 2008, but a portable solution would be ideal.

Thanks!

What RDBMS are you using? MySQL, Postgres, sql server 2008, etc? — JNevill
– JNevill, Commented Mar 9, 2016 at 22:22
Great! Thanks :) Max's answer should do the trick for 2008. Window Functions are the way to go for this type of thing. 2008's implementation of Window Functions is pretty limited, but I believe Row_Number() was in place for that version. — JNevill
– JNevill, Commented Mar 9, 2016 at 22:26

Max Szczurek · Accepted Answer · 2016-03-09 22:27:03Z

3

select id, fruit, row_number() over (partition by fruit order by id) as running_total
from fruits
order by id

And then,

alter table Fruits add RUNNING_TOTAL int null

update fruits set running_total = subquery.running_total
from fruits
inner join (
 select id, row_number() over (partition by fruit order by id) as running_total
 from fruits
 )subquery on fruits.id = subquery.id

select * from fruits

edited Mar 9, 2016 at 22:27

answered Mar 9, 2016 at 22:21

Max Szczurek

4,3342 gold badges23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-03-09 22:29:29Z

2

In SQL Server 2008, you can use an updatable CTE:

with toupdate as (
      select f.*, row_number() over (partition by fruit order by id) as seqnum
      from fruits f
     )
update toupdate
    set running_total = seqnum;

I wouldn't really call such a column "running_total". It seems more like a "sequence_number" to me. "Running_total" suggests a cumulative sum.

answered Mar 9, 2016 at 22:29

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

George Over a year ago

Thank you, Gordon. Will try your solution tomorrow. +1 to you. Also good comment on the column name. This is not the real column name I will use, but for the example purposes, sequence is indeed better.

Collectives™ on Stack Overflow

How to calculate "running total" in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related