Addition Of previous two rows into new row

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

Addition Of previous two rows into new row

Author

Topic

Shadab Shah
Starting Member

23 Posts

Posted - 2012-07-19 : 01:00:33

I am a newbie and was trying around and come up with this problem:
Suppose i have a table A having column as:
col1
100
350
-120
100

i want the output as
col1
100
450
330
430

The output is actually addition of 100+350=450,450+(-120)=330,330+100=430.

And i have only this column in my table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 02:35:09

how do you determine the order ?

There isn't any specific sequence or order of how record is stored in the table. You determine how do you want the records to return using the ORDER BY clause in the SELECT statement

KH
[spoiler]Time is always against us[/spoiler]

Shadab Shah
Starting Member

23 Posts

Posted - 2012-07-19 : 02:49:11

Now that's the challenge over here. I want to achieve the above output with-out binding it with order by or groupby in the select statement. Actually i don't know whether this can be achieve though SQL Server or not

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 02:58:19

quote:
Originally posted by Shadab Shah

Now that's the challenge over here. I want to achieve the above output with-out binding it with order by or groupby in the select statement. Actually i don't know whether this can be achieve though SQL Server or not

if you have only one column, how do you determine the required sequencing of the record return ?

KH
[spoiler]Time is always against us[/spoiler]

Shadab Shah
Starting Member

23 Posts

Posted - 2012-07-19 : 05:42:20

i think if i am able to understand what you mean to say then my answer would be : The sequence should be 100 , 450(100+350) ,330(450+(-120)) , 430(330+100)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-19 : 06:03:36

quote:
Originally posted by Shadab Shah

i think if i am able to understand what you mean to say then my answer would be : The sequence should be 100 , 450(100+350) ,330(450+(-120)) , 430(330+100)

not exactly. what i am saying is, in order to get the records to return in the same ordering manner every time you retrieve it is to use the ORDER BY in the query
example
SELECT col1 FROM yourtable ORDER BY col1
or
SELECT col1 FROM yourtable ORDER BY col1 DESC

if you want it to be in descending order.

Without that ORDER BY clause, the sequence of records return is not guaranteed.

As you said you have only one column in that table, you can only ORDER BY col1. And the result is not what you might want it to be. Unless you have a ID identity column in that table, than it will be a different story
example :

then you can order by that ID column
SELECT ID, col1 FROM yourtable ORDER BY ID

KH
[spoiler]Time is always against us[/spoiler]

Shadab Shah
Starting Member

23 Posts

Posted - 2012-07-19 : 06:12:56

I may still be not able to get you but over here i want addition of previous rows and i don't see any interlink between them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources