How To Sum...

Question

How To Sum...

sarwaanmca

Right there with Babe

Points: 796
More actions
April 24, 2013 at 5:08 am

#277010

I Having Following Table Structure....
Create Table Adding (ID int identity(1,1),Result int,ActualResult int)
insert into Adding (Result) values (10),(10),(10),(10),(-10),(-10),(-10),(-10)
select * from Adding
But My Required Result Should be below Mentioned...
IDResultRequiredResult
1100
21020
31030
41040
5-1030
6-1020
7-1010
8-100
Example :
----------
{
( Result + ActualResult ) = ActualResult Then ( ActualResult+ Result ) = ActualResult .....
10 + 0 = 10 Then
10 +10 = 20 Then
20 +10 = 30 like that is going....
}
Thanks & Regards,
Saravanan.D

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You will probably need a trigger which will update the value of the 3rd column when you enter or update a row in your table.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sharky Default port Points: 1494 More actions · Answer 2

One set based way of doing it:

DECLARE @Adding TABLE (ID int identity(1,1),Result int,ActualResult int)

INSERT INTO @Adding (Result) VALUES (10),(10),(10),(10),(-10),(-10),(-10),(-10)

SELECT ID, Result, (Select SUM(result) From @Adding A2 WHERE A2.Id <= A1.Id) As ActualResult from @Adding A1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

sharky (4/24/2013)
One set based way of doing it:
DECLARE @Adding TABLE (ID int identity(1,1),Result int,ActualResult int)
INSERT INTO @Adding (Result) VALUES (10),(10),(10),(10),(-10),(-10),(-10),(-10)
SELECT ID, Result, (Select SUM(result) From @Adding A2 WHERE A2.Id <= A1.Id) As ActualResult from @Adding A1

This Triangular Join method and others are subjected to comparative testing and analysis by Jeff Moden here[/url]. The TJ method tends to perform and scale poorly with unpartitioned data i.e. where the entire table has to be scanned for the last result calculated.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sharky Default port Points: 1494 More actions · Answer 4

Agree. Simple method for a small resultset. Best performance is still a CLR based one.