Ruchi Ruchi - 1 month ago 14
SQL Question

Cumulative Additions in SQL Server 2008

I am trying to add the previous row value to the current row and keep this adding until I reach the total.

I have tried the below query and I have set row number to the rows as per my needs, the additions should take place in the same manner.

select *
from #Finalle
order by rownum ;


Output:

Type Count_DB Rownum
------------------------------------------
Within 30 days 399480 1
Within 60 days 30536 2
Within 90 days 10432 3
Within 120 days 11777 4
Greater than 120 days 13091 5
Blank 29297 6
Total 494613 7


When I try the below query, it works fine until the 6th row, but fails for the last row:

select
f1.[type],
(select
Sum(f.[Count of ED_DB_category]) as [Cummumative]
from
#Finalle f
where
f1.rownum >= f.rownum)
from
#Finalle f1
order by
rownum


Output:

Type No column name
--------------------------------------
Within 30 days 399480
Within 60 days 430016
Within 90 days 440448
Within 120 days 452225
Greater than 120 days 465316
Blank 494613 <---Add only until here
Total 989226


Here the total should return the same value as in the first table.

How do I achieve this?

Answer

You may looking for this

select f1.[type],
       (select Sum(f.[Count of ED_DB_category])  
               from #Finalle f where f1.rownum >= f.rownum AND 
                f.Type <> 'Total'
       )as [Cummumative]
from #Finalle f1
order by rownum
Comments