update row with previous rows value

Question

0.00/5 (No votes)

See more:

i have variable Table

SQL

declare @Table Table
(
Value1 float,
Value2 float,
Total float
)
Insert into @Table values (1,1)
Insert into @Table values (2,2)
Insert into @Table values (3,3)
Insert into @Table values (4,4)
Insert into @Table values (5,5)

i need the total column to be the sum(value1+value2) from the current row +Previous Rows

so the result 'll be like that

Value1 Values Total
---------------------
1 1 2
2 2 6
3 3 12
4 4 20
5 5 30

so how i can calculate the total
i don't want to use loop because in the real example i have about 2000 rows or more so of i use any loop statement it 'll took too much time
i need to keep the performance good

[code] block updated

Posted 10-Jul-12 17:57pm

md_refay

Updated 10-Jul-12 18:09pm

AshishChaudha

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pablo Aliskevicius · Answer 1 · 2012-07-10T19:33:00

Solution 1

Try this:
Calculating simple running totals in SQL Server[^]

Hope this helps,
Pablo.

Posted 10-Jul-12 19:33pm

Pablo Aliskevicius

Vasim889 · Answer 2 · 2012-07-10T20:01:00

Solution 2

SQL

create trigger trig_product
on table
after insert
as 
declare @input varchar(25)
set @input = (select distinct    max(Total)  from  table)
update  table
	set  Total = @input 
	from  table where ID=(select max(ID) from Table)

Posted 10-Jul-12 20:01pm

Vasim889