Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to add two column and the to put the result on the next row? Expand / Collapse
Author
Message
Posted Friday, February 1, 2013 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 2, 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
Posted Monday, February 4, 2013 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1415326
Posted Tuesday, February 5, 2013 7:06 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:29 AM
Points: 163, Visits: 470
Maybe you could use the sql statement below:

update tablename set column3 = column1 + column2


Post #1415816
Posted Tuesday, February 5, 2013 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1415832
Posted Wednesday, February 6, 2013 10:58 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 11:22 PM
Points: 111, Visits: 22
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
Posted Wednesday, February 6, 2013 11:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
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
Posted Thursday, February 7, 2013 8:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:39 PM
Points: 3,640, Visits: 5,287
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.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1417435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse