I have the following table:
CREATE TABLE tbl_proc(
[proc] float,
subscriber bigint
)
:
proc | subscriber
-----|-----------
0.7 | 123456
0.5 | 1234567
0.3 | 12345
0.3 | 45678
0.3 | 1234
0.2 | 123455
0.1 | 894562
I would like to find a good method for adding a new column to a table that represents the sum of the above values.
Result:
proc | subscriber | col3
-----|------------|------------
0.7 | 123456 | 0.7
0.5 | 1234567 | 1.2 -- 0.7 + proc
0.3 | 12345 | 1.5
...
I found the following method:
Select a.[proc],SUM(b.[proc])
from tbl_proc a, tbl_proc b
where a.[proc] <= b.[proc] and (a.[proc] <> b.[proc] or a.subscriber >= b.subscriber)
group by a.[proc],a.subscriber
order by a.[proc] desc
In my table, the data is sorted in descending order. Also, the subscriber column is unique.
This method I found is a bit expensive (my tables are large). Due to performance considerations, I did not consider a th-dimensional solution.
Any suggestions?
Update:
I looked for the problem a bit more and I found the solution "Update local variable" on this page:
http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx
As far as I checked, this is the best solution.
declare @runningTotal float = 0
UPDATE tbl_proc
SET @RunningTotal = new_col = @RunningTotal + [proc]
FROM tbl_proc