Creating commulative percent coloumn 1

[erezba]

Hi

I need to create a new column in a table, that will hold a commulative percent values.

Let's assume we have a table of this structure :

Month Sales Percent
1 1000 0.1
2 7000 0.7
3 2000 0.2

I have calculated the 3'rd colomn (Percent).
Now what I need is a commulative percent column, meaning:

ComPer
0.1
0.8
1

Can you please assist me in building such a column?
Esch value in this column depends on the value before.

I wonder if a cursor holds the answer, but since I have about 3 million records, I assume cursors is not the preffered solution (if it's a solution at all).

Thanks in advance
Erez

 

[jbenson001]

This type of cumulative summing should probably be done in a front-end application or reporting tool.

 

[markros]

Search for 'Running Total' problem on the web.

 

[bborissov]

OK, you need this column, but how you know from where you start and stop the percentage calculation?
Why not just calculate it in the query and not keep it in the table?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I found this thread here

http://tek-tips.com/viewthread.cfm?qid=1495550&page=1

and here is another good link on the problem

http://www.sqlteam.com/article/calculating-running-totals

 

[RyanEK]

declare @test table (mon int, perc float)  
insert into @test values (1, 0.1)
insert into @test values (2, 0.7)
insert into @test values (3, 0.2)

select *,
(select sum(t2.perc) 
 from @test t2 
 where t2.mon <= t1.mon) as runtotal
from @test t1

 

[erezba]

Thanks,
RyanEK

I thoght of a solution similar to yours , even without the subquery . I can join a table to itself and using row_num value sum the rows less than the current row_num (similar to what you wrote).
However I am dealing with about 2 million records, so the query consumes resources the server can't supply.

The easier solution I found was a very rare(to me at least)
, of update table command.

@decalre tmp float
set @tmp=0
Update table1
set @tmp = columntobeupdated = @tmp + currentcolumnvalue

this workes well and quick.

Thanks anyway for your kind help.
I hope this post will help others as well.

Erez