February 17, 2009 at 11:17 pm
reate table temp (id int identity(1,1),Sum1 int)
INSERT INTO TEMP(Sum1) select 10
INSERT INTO TEMP(Sum1) select 20
INSERT INTO TEMP(Sum1) select 30
INSERT INTO TEMP(Sum1) select 40
INSERT INTO TEMP(Sum1) select 50
INSERT INTO TEMP(Sum1) select 60
I should get the output as given below
ID Sum1
110
230
360
4100
5150
6210
Every row I should get the Sum of Sum1 from the prededing row
How can I do this?
DROP TABLE Temp
February 18, 2009 at 4:42 am
create table #tmp (fldID int identity(1, 1), fldNum int)
insert into #tmp (fldNum)
select 1 as x union all
select 2 as x union all
select 3 as x union all
select 4 as x union all
select 5 as x union all
select 6 as x union all
select 7 as x union all
select 8 as x union all
select 9 as x
select A.fldID, A.fldNum, (select isnull(sum(B.fldNum), 0) from #tmp B where B.fldID < A.fldID) as mySum from #tmp A
drop table #tmp
February 18, 2009 at 5:03 am
The output I get for this is
id sum1 Mysum
1100
22010
33030
44060
550100
660150
Which is not my requirement?
February 18, 2009 at 5:19 am
Try This:
select b.sum1,sum(a.sum1) as cum_sum
from temp a,temp b
where a.id <= b.id
group by b.id,b.sum1
--where cum_sum will be your cumulative sum
February 18, 2009 at 5:39 am
Thanks this is what I wanted.
February 18, 2009 at 5:56 am
Ashwin M N, please forgive the flames but it had to have taken you longer to write that the answer didn't match your requirements than it would have taken for you to apply the answer to your requirements.
What ever happened to trying?
March 4, 2009 at 12:21 am
Actually, all of the answers given use a performance crushing technique that will kill a server with relatively few rows. It's called a "Triangular Join" and it can be thousands and millions of times worse than a cursor with as little as 10,000 or 20,000 rows. Forget it if you have more than that. Read all about in the following article...
http://qa.sqlservercentral.com/articles/T-SQL/61539/
... and then tell me how many rows you have in your real table so we can put some peformance into the task.
--Jeff Moden
March 4, 2009 at 6:44 am
Is this a better approach, Jeff?
set nocount on
set statistics io on
set statistics time on
-- Drop if necessary
drop table #tbl
create table #tbl (IID int identity(1, 1), num int)
create clustered index tmpIndexIID on #tbl(IID)
insert into #tbl(num) values (0)
while(select count(IID) from #tbl) < 10000
insert into #tbl (num) select num from #tbl
-- Assign the num value based on the ID
update #tbl set num = IID
-- How to get a sum of all records prior to each record
select A.num, sum(B.num) as rowsum from
#tbl A inner join #tbl B on A.IID >= B.IID
group by A.num order by A.num
-- Yields the following stats:
-- Table '#tbl00000000822A'. Scan count 16386, logical reads 306674,
-- physical reads 0, read-ahead reads 0.
--SQL Server Execution Times:
-- CPU time = 158249 ms, elapsed time = 79962 ms.
March 4, 2009 at 8:21 am
I knew I remembered a way to do this with variables!
Credit where it's due, Jeff showed me this a while ago. It took me a while to remember it.
set nocount on
set statistics io on
set statistics time on
-- Drop if necessary
drop table #tbl
create table #tbl (IID int identity(1, 1), num int, rowsum bigint)
create clustered index tmpIndexIID on #tbl(IID)
insert into #tbl(num, rowsum) values (0, 0)
while(select count(IID) from #tbl) < 500000
insert into #tbl (num, rowsum) select num, rowsum from #tbl
-- Assign the num value based on the ID
update #tbl set num = IID
declare @sum bigint
set @sum = 0
update #tbl set @sum = rowsum = (@sum + num)
select * FROM #tbl order by IID
-- The update stats:
-- Table '#tbl00000000822A'. Scan count 1, logical reads 1626, physical
-- reads 0, read-ahead reads 0.
--SQL Server Execution Times:
-- CPU time = 2125 ms, elapsed time = 2190 ms.
-- on 524,000 + rows
March 4, 2009 at 4:07 pm
souLTower (3/4/2009)
I knew I remembered a way to do this with variables!
Heh... yep... that's the way, ST. Just gotta remember that you need to add a WITH (INDEX(0)) (didn't tell you about that before) to the fray to force the index scan should it not actually happen by itself...
update #tbl set @sum = rowsum = (@sum + num)
FROM #tbl WITH (INDEX(0))
Since that will eventually lock the whole table, save the server a couple of clock cycles... do it up front....
update #tbl set @sum = rowsum = (@sum + num)
FROM #tbl WITH (INDEX(0),TABLOCKX)
--Jeff Moden
March 5, 2009 at 7:56 am
Jeff, quick question. Since I used a clustered index couldn't I "assume" that the order would be as I want it?
I know to never assume...
ST
March 5, 2009 at 1:35 pm
souLTower (3/5/2009)
Jeff, quick question. Since I used a clustered index couldn't I "assume" that the order would be as I want it?I know to never assume...
ST
Correct... never assume unless you actually want bad data some day. 😀
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply