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

3 replies

Last post Nov 19, 2012 04:52 AM by sajid007

Member

349 Points

111 Posts

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

Nov 17, 2012 08:22 AM|LINK

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 ,

ali

Contributor

3998 Points

725 Posts

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

Nov 17, 2012 09:38 AM|LINK

Hello,

What is your SQL table stracture and what you are trying to achive?? your que is not clear, Please clerify more

let me know if any query

Thanks,
Gaurav Dhol
Skype ID : dhol.gaurav
If My Post contains helped you, Please Mark as Answer

Contributor

6779 Points

1058 Posts

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

Nov 17, 2012 10:14 AM|LINK

I have provided the solution with sample data. Place your exisitng query in cte, keeping the rownum column as it is...

declare @tab table ([User Name] varchar(100), [Total Amount] int, [Paid Amount] int)
insert into @tab
select 'abc',15000,500 union all
select 'xyz',25000,20000 union all
select 'mnp',35000,30000

;with cte as (
select [User Name], [Total Amount], [Paid Amount], ROW_NUMBER() OVER (ORDER BY [User Name]) AS rownum
from @tab
)

select a.[User Name], a.[Total Amount], a.[Paid Amount]
, sum(b.[Paid Amount]), sum(b.[Total Amount]- b.[Paid Amount])
from cte a left join cte b on b.rownum <= a.rownum
group by a.[User Name], a.[Total Amount], a.[Paid Amount]

Also refer below links for cumulative sum
http://itdeveloperzone.blogspot.in/2011/03/cumulative-sum-in-sql-server.html
http://itdeveloperzone.blogspot.in/2012/02/running-total-in-sql-server.html

Sandeep Mittal | My Blog - IT Developer Zone

Member

349 Points

111 Posts

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

Nov 19, 2012 04:52 AM|LINK

Thanks Brothers,

with the references of your replys n serch i got the solutions from following URL,

http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum

ali
• Marked as answer by sajid007 on Nov 19, 2012 04:52 AM