Cumulative Total in MS Sql server [duplicate]

Possible Duplicate:
Calculate a Running Total in SqlServer

I need to get the cumulative (running) total of a column in ms-sql server. I.e. if there is a column named “Marks”, then corresponding to each row cumulative sum will the sum of current and previous rows. Can we attain the result without using joins? Because my query is pretty big.

I have included a sample table and data:

CREATE TABLE "SCORE_CHART" 
   (    
        "STUDENT_NAME" NVARCHAR(20), 
        "MARKS" INT
   )

INSERT INTO SCORE_CHART (STUDENT_NAME, MARKS) VALUES ('STUD1', 95);
INSERT INTO SCORE_CHART (STUDENT_NAME, MARKS) VALUES ('STUD2', 90);
INSERT INTO SCORE_CHART (STUDENT_NAME, MARKS) VALUES ('STUD3', 98);

SELECT STUDENT_NAME, MARKS FROM SCORE_CHART;

Expected result:

In oracle it’s easy to write like:

SELECT 
  STUDENT_NAME,
  MARKS,
  SUM(MARKS) OVER (ORDER BY STUDENT_NAME) CUM_SUM
FROM SCORE_CHART
ORDER BY STUDENT_NAME;

Thanks in advance.

The same query is supported from 2012 onwards. In older versions there are several approaches. Refer this http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

try this:

you can get the cumulative sum just by joining the same table itself

SELECT S1.STUDENT_NAME, S1.MARKS ,sum(S2.MARKS) CUM_SUM
FROM SCORE_CHART S1 join SCORE_CHART S2
on S1.STUDENT_NAME>=S2.STUDENT_NAME
group by S1.STUDENT_NAME, S1.MARKS 
order by S1.STUDENT_NAME, S1.MARKS

SQL Fiddle demo

You said no joins, what about a apply? ;)

SELECT STUDENT_NAME, MARKS, running.total
FROM SCORE_CHART a
cross apply 
(
    select SUM(marks) total 
    from score_chart b
    where b.student_name <= a.student_name
) running
ORDER BY STUDENT_NAME;

With a index on student_name speed should be okay!

Check the query for Recursive CTE.

;with CTE as (select ROW_NUMBER() over (order by (select 0)) as id,STUDENT_NAME,MARKS from SCORE_CHART)
,CTE1 as (

select id,STUDENT_NAME,marks,marks as CUM_SUM from CTE where id=1
UNION ALL
select c.id,c.STUDENT_NAME,c.marks,c.marks+c1.CUM_SUM as CUM_SUM from CTE1 c1 inner join CTE c on c.id-1=c1.id)
select * from CTE1