Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to add two column and the to put the result on the next row?


How to add two column and the to put the result on the next row?

Author
Message
angjoni
angjoni
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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]
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
HildaJ
HildaJ
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 571
Maybe you could use the sql statement below:

update tablename set column3 = column1 + column2
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
ashishjain
ashishjain
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search