Adding two rows of a column display result in another column in same table

Question

Adding two rows of a column display result in another column in same table

sajidsdali

SSC Rookie

Points: 31
More actions
November 17, 2012 at 2:42 am

#267058

Hi Friends,
I need your help
i need to show the result table as follows,
User Name Total Amount Paid Amount Total Paid Amount Total DueAmount
abc 15000 500 500 14500
xyz 25000 20000 20500 19500
mnp 35000 30000 50500 24500
no wmy question is first three columns data coming from different tables when i have to show the 4 th and 5 th column i am getting problems.
4 th column data has to come from sum of 3rd column
ex: 20500 is equal to 3 rd column 1 st row + 2 nd row
5 th column data has to come from ((sum of 2 nd column)-(sum of 3 rd column))
ex : 19500 is equal to =((sum of 2 nd column)-(sum of 3 rd column))
=((40000)-(20500))
=19500
so Please give me ideas or solutions ,
Thanks in Advance............

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 1

Search for "running total" on this site. In sql 2012 it is trivial with OVER() clause, but in sql 2008 or before you will have to use a self-join.

Always give complete test case (tsql script) to get better answers.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

CapnHector SSCoach Points: 16143 More actions · Answer 2

Vedran Kesegic (11/17/2012)
Search for "running total" on this site. In sql 2012 it is trivial with OVER() clause, but in sql 2008 or before you will have to use a self-join.
Always give complete test case (tsql script) to get better answers.

it may also just be a calculated column based on the 2 amount columns where SELECT column1 - column2 will work. Either way sample data and expected results will help solve the problem much quicker. See the first link in my signature on the best way to ask questions to get fast results.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Vedran Kesegic (11/17/2012)
Search for "running total" on this site. In sql 2012 it is trivial with OVER() clause, but in sql 2008 or before you will have to use a self-join.
Always give complete test case (tsql script) to get better answers.

Oh, be careful now. I believe that you're talking about a self-join with an aggregate and that makes for a "Triangular Join". Please see the article at the following article for why those are so very bad.

http://www.sqlservercentral.com/articles/T-SQL/61539/

There are many supported methods to do a running total prior to 2012. "Firehose" cursor, While Loop, recursive CTE, and the unsupported but incredibly high performance "Quirky Update" method any one of which will blow the proverbial doors off a self-joined aggregate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

sajidsdali (11/17/2012)
Hi Friends,
I need your help
i need to show the result table as follows,
User Name Total Amount Paid Amount Total Paid Amount Total DueAmount
abc 15000 500 500 14500
xyz 25000 20000 20500 19500
mnp 35000 30000 50500 24500
no wmy question is first three columns data coming from different tables when i have to show the 4 th and 5 th column i am getting problems.
4 th column data has to come from sum of 3rd column
ex: 20500 is equal to 3 rd column 1 st row + 2 nd row
5 th column data has to come from ((sum of 2 nd column)-(sum of 3 rd column))
ex : 19500 is equal to =((sum of 2 nd column)-(sum of 3 rd column))
=((40000)-(20500))
=19500
so Please give me ideas or solutions ,
Thanks in Advance............

Hi sajidsdali ,

How many rows are we talking about in the final result?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)