sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query

Author

Topic

aj007
Starting Member

1 Post

Posted - 2012-08-11 : 05:10:53

Hello,

I have one scenario that i'm working on and it is like this

Table look like this

Field1 Field2 Field3 Sum
1 2 3 6
2 3 4 9
3 1 3 7

The output i'm specifically lookin' for is like this

Field1 Field2 Field3 Sum
1 2 3 6
6 3 4 13
13 1 3 17

so the sum in the previous row should be the value in Field1 for the subsequent row.The value in Field1 for the first row would remain unchanged.

Please help me with my query.

Thanks,
AJ

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-11 : 08:25:44

How do you decide the ordering of the rows? In a SQL, by definition, rows in a table form an unordered collection. So you have to have some way of ordering the data to do the computations that you described. The ordering could be based on some of the existing columns, or may be you have another column that determines the sorting order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-11 : 10:30:46

for the sample data it looks like this


;With CTE
AS
(
SELECT Field1,Field2,Field3,Field1 + Field2+ Field3 AS Total,
ROW_NUMBER() OVER (ORDER BY Field1) AS Seq
FROM Table
)

SELECT COALESCE(c2.Total,c1.Field1),c1.Field2,c1.field3,COALESCE(c2.Total,c1.Field1)+ c1.Field2 + c1.Field3 AS [Sum]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.Seq= c1.Seq-1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources