Want to add rows values to show in a new row

Question

Hi I have a problem I want to add the rows values. I have rows in table like Months Values Jan-2010 5 Feb-2010 10 Mar-2010 7 Apr-2010 2 Logic for the result: Jan-2010 - 5 Feb-2010 -15 (Jan-2010 + Feb-2010) Mar-2010 -22 (Jan-2010 + Feb-2010 + Mar-2010) Apr-2010 -2 (Jan-2010 + Feb-2010 + Mar-2010 + Apr-2010) I want results set like this Months Values Jan-2010 5 Feb-2010 15 Mar-2010 22 Apr-2010 24 Pls help on urgent basis Thanks in Advance Regards Anurag

Syed Nayab Ali · Answer

Hi Anurag to achive this result you should need a Identity column use following logic I hope it will help you select IDENTITY ( int  1  1 ) AS ID_Num  value  Months into #temp from temp --- it create a temporary table with 3 columns (ID_Num Value Months ) sekect * from #temp select a . ID_num  a . months  SUM ( b . value ) from #temp a join #temp b on b . id_num a . id_num group by a . ID_num  a . months Thanks Please click on  Accepted Answer  if it helps you

Syed Nayab Ali · Answer

Hi Anurag Try to use following concept It will give you proper result SELECT a . SrNo as Srno  a . Months as Months  SUM ( a . value ) as Value into #temp FROM Tablename a WITH ( NOLOCK ) GROUP BY a . SrNo  a . Months Select aaa . srno  aaa . Months  SUM ( b . total ) as value from #temp aaa join #temp bbb on bbb . srno aaa . srno group by aaa . srno  aaa . Months Thanks Please click on  Accept A nswer  if it helps you

Anurag Srivastava · Answer

Thanks for the help Nayab. But my problem changes suddenly Want to add row data and GROUP the data by using SrNo and Months. SrNo Months Value 1 Jan-2010 2 1 Jan-2010 5 2 Feb-2010 1 2 Feb-2010 6 2 Feb-2010 2 3 Mar-2010 7 3 Mar-2010 4 The result set should be like this: SrNo Months Value 1 Jan-2010 7 (Sum of Jan-2010 value) 2 Feb-2010 16 (Jan-2010 + Feb-2010) 3 Mar-2010 27 (Jan-2010 + Feb-2010 + Mar-2010) Help on urgent basis.Ur help will highly appreciated Thanks in advance Regards Anurag

Javeed M Shaikh · Answer

Nayab Ali's solution will work only if the data in your table is guranteed to be in that order on select.

Javeed M Shaikh · Answer

hi Anurag is there a ID column which we can consider?