cummulative sum
-
hi,
I am in a tight corner, i need an sql statement that will help me calculate something. Now i have a table with a column A and i want the cummulative sum of Column A to appear in Column B such that for every row of column B it is have the cummulative sum of column A above it.
Can someone ples help me out
i don't know what to do
thanks
Eno
-
Hi Eno,
Quick clarification.
Col a Col b
2 2
2 4
5 9
Is this what you require?
Steve
We need men who can dream of things that never were.
-
See, if this helps:
set nocount on
create table #rt
(
c1 int identity
, c2 int
)
insert into #rt values (1)
insert into #rt values (2)
insert into #rt values (3)
insert into #rt values (4)
insert into #rt values (5)
insert into #rt values (6)
insert into #rt values (7)
select
t1.c2
, RunningSum = sum(t2.c2)
from
#rt t1 cross join #rt t2
where
(t2.c1 <=t1.c1)
group by t1.c2, t1.c1
order by
t1.c1, t1.c2
drop table #rt
set nocount off
c2 RunningSum
----------- -----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
Note, that the use of a CROSS JOIN isn't very fast on larger tables, but I can't figure out a more effective solution right now.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Following on from Frank's post
select t1.c2, sum(t2.c2) as [RunningSum]
from #rt t1
inner join #rt t2
on t2.c1 <= t1.c1
group by t1.c1, t1.c2
order by t1.c1
Has the table got unique key denoting order?
Far away is close at hand in the images of elsewhere.
Anon. -
Same execution plan, Dave! The optimizer is bright enough to figure this out. Damn, can't remember where I saw a neat, handy solution for this (apart from doing this at the client, of course)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Gents,
I have definately seen this in these forums somewhere before.
All the best
Steve
We need men who can dream of things that never were.
-
OK, OK, how about this then
declare @sum int
set @sum = 0
update
set @sum = [col b] = @sum + [col a]
Far away is close at hand in the images of elsewhere.
Anon. -
This is a neat trick, Dave.
However, it has a major disadvantage, which effectively rules this out here as an option. You are not able to explicitely force an ordering of the rows before the UPDATE, so the UPDATE happens quite randomly.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes true Frank, the order cannot be guaranteed. However all the times I have done this sql has always updated in order of the rows in the heap but as you say it cannot be guaranteed. The only two ways I know of doing this is by using a join or a correlated query to sum the values of preceeding rows.
select t1.c2,
(select sum(t2.c2) from #rt t2 where t2.c1 <= t1.c1) as [RunningSum]
from #rt t1
order by t1.c1
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply