Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server Newbies » How to add two column and the to put the...
Add to briefcase

How to add two column and the to put the result on the next row? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
angjoni
Posted Friday, February 01, 2013 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:10 AM
Points: 1, Visits: 5

column1--- column2--- result

50 ------ 1500----- 1550

100----- - 1550----- 1650

40----- - 1650----- 1690

30----- - 1690----- 1720



Now I do have the value of the 'column1' and I need to generate the values for the 'column2' and 'result'

I do have the initial value of the 'column2' (1500).

I need to add 'column1' to 'column2' and generate the 'result'.

Then I put the value of the 'result' from first row to the second row of the 'column2', after that I add the second value from 'column1' and so I generate the second value for the 'result' and so on.

I am new in SQL server and I am running out of ideas for this complicated problem.

I would appreciate any sugestion

Thanks in advance.[center]

[/center]
Post #1414909
Sean Lange
Posted Monday, February 04, 2013 8:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523
Hi and welcome to SSC. What you are describing is a running total. Here is an article that explains one way of doing this.

http://www.sqlservercentral.com/articles/68467/

If you find that you need more specific help with coding please first take the time to read the article at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1415326
HildaJ
Posted Tuesday, February 05, 2013 7:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 104, Visits: 287
Maybe you could use the sql statement below:

update tablename set column3 = column1 + column2
Post #1415816
Sean Lange
Posted Tuesday, February 05, 2013 7:25 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523
HildaJ (2/5/2013)
Maybe you could use the sql statement below:

update tablename set column3 = column1 + column2




That doesn't accomplish the running total like the OP is looking for.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1415832
ashishjain
Posted Wednesday, February 06, 2013 10:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:10 AM
Points: 86, Visits: 9
Hi angjoni,

You can use below code to perform this operation:

create table test99 (column1 int, column2 int, result int)
insert into test99
values
(50,1500,0),
(100,0,0),
(40,0,0),
(30,0,0)

declare @col2 int = 1500
select * from test99

update test99
set @col2 = result = @col2 + column1,
column2 = @col2
from test99

select * from test99

drop table test99
Post #1416811
Koen Verbeeck
Posted Wednesday, February 06, 2013 11:30 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 9,410, Visits: 6,495
ashishjain (2/6/2013)
Hi angjoni,

You can use below code to perform this operation:

create table test99 (column1 int, column2 int, result int)
insert into test99
values
(50,1500,0),
(100,0,0),
(40,0,0),
(30,0,0)

declare @col2 int = 1500
select * from test99

update test99
set @col2 = result = @col2 + column1,
column2 = @col2
from test99

select * from test99

drop table test99



This is the quirky update method. Very fast indeed, but you must adhere to some rules to garantuee consistent behaviour. The article Sean linked to has a very good description of the method and its rules.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1416818
dwain.c
Posted Thursday, February 07, 2013 8:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 2,369, Visits: 3,247
ashishjain (2/6/2013)
Hi angjoni,

You can use below code to perform this operation:

create table test99 (column1 int, column2 int, result int)
insert into test99
values
(50,1500,0),
(100,0,0),
(40,0,0),
(30,0,0)

declare @col2 int = 1500
select * from test99

update test99
set @col2 = result = @col2 + column1,
column2 = @col2
from test99

select * from test99

drop table test99



I'll add an explicit warning to what Koen said:

While it may work in the example case, the above code violates at least 3-4 of the rules in that article.

I won't tell you what they are because you need to read that article and understand the rules before you use this approach in a production system.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1417435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.