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

• • Reply
  

  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 ,

   

   

  Thanks in Advance............

  ali
• • Reply
  

  dhol.gaurav

  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
• • Reply
  

  sandeepmitta...

  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
  
  
  • Edited by sandeepmittal11 on Nov 17, 2012 10:19 AM
  • Edited by sandeepmittal11 on Nov 17, 2012 10:20 AM
  • Edited by sandeepmittal11 on Nov 17, 2012 10:20 AM
• • Reply
  

  sajid007

  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