SQL Server 2000 - Generating new field containing the sum of other rows

Transact-SQL

ajliaks


Hi guys,
This one seems to be a difficult one:
I have a table called "myTable" with a field called "myField" (which contains only numbers).
I need to generate a new field where each row is the sum of the row in field "myField" plus all the values in the rows before it. something like this:
myField NewField
100 100 = 100+0
200 300 = (200+100)
300 600 = (300+200+100)
400 1000 = (400+300+200+100)
500 1500 = (500+400+300+200+100)
I'll apprecciate any smart help.
Aldo.



Top

Re: SQL Server 2000 - Generating new field containing the sum of other rows

lkh


There isn't enough information in your post to give a certain answer. Typical approaches are to use a cursor, see Books Online, or something lke the update below.

CREATE TABLE dbo.RunningTotal

(

Entry int

,RunningTotal int

)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(100,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(200,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(300,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(400,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(500,NULL)

UPDATE dbo.RunningTotal

SET RunningTotal = RT2.RunningTotal

FROM dbo.RunningTotal RT1

INNER JOIN

(

SELECT Entry

,(SELECT SUM(Entry) FROM dbo.RunningTotal WHERE Entry <= rt.Entry) As RunningTotal

FROM dbo.RunningTotal rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.RunningTotal

Resultset:

Entry RunningTotal

100 100

200 300

300 600

400 1000

500 1500






Top

Re: SQL Server 2000 - Generating new field containing the sum of other rows

ajliaks

Hi lkh, Thanks for answering.
I have never used the code to create a table yet, but I understand from your code that you are creating a new Table and entering each value "manually"
The table with the original data "myTable", has several fields with thousands of data... what should I do
Thanks,
Aldo.





Top

Re: SQL Server 2000 - Generating new field containing the sum of other rows

lkh

I can't answer that question without knowing the structure of your table, the data in your table and exactly what operation you are trying to perform. This code will createa running total for any column. I created a small table and inserted data so you could run the same code on your computer and uncerstand the concept.



Top