How to calculate the running total of a column in SQL Server 2008 R2?

Question

I have tried to find the whole sum of a column in the SQL Server 2008 R2 after sorting the column in the ascending order. I can't use the rows unbounded preceding or following as it is just available for the SQL Server 2012 and new versions. 

I used the below code :

select
    sub_code,
    Roll_no,
    Total_marks,
    sum (Total_marks) over (order by Total_marks ASC) as cumulative_Total  
from table

But I am getting the following error:

Incorrect syntax near 'order'

How can I resolve this?

Answer 1

One choice which does not use a window function would be to use a correlated subquery to calculate the running total:

select
    t1.sub_code,
    t1.Roll_no,
    t1.Total_marks,
    (select sum(Total_marks) from table t2
     where t2.Total_marks <= t1.Total_marks) as cumulative_Total
from table t1
order by t1.Total_marks

This implies that you actually need to order the running total using the thing you are attempting to sum, namely the Total_marks. In general, you could use the below-correlated subquery if you plan to use a different column for ordering:

(select sum(Total_marks) from table t2
where t2.some_col <= t1.some_col) as cumulative_Total

Want to Learn SQL to get expertise in the concepts of SQL? Join the SQL course and get certified after clearing SQL certification exam.