CODEWITHSUNDEEP
sqlsql-serversql-server-2008
George
George

Reputation: 2594

How to calculate "running total" in SQL

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!

Upvotes: 1

Views: 88

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 2

Max Szczurek
Max Szczurek

Reputation: 4334

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

Upvotes: 3

Related Questions