Cumulative Total in MS Sql server [duplicate]

Question

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.

Answer 1

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!

Answer 2

Use Recursive CTE to achive this.

Answer 3

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

Answer 4

JUSt doing a join doesn't seem to guarantee order but comes up with the final answer ok:

select 
  x.STUDENT_NAME
  , sum(y.marks) marks
from 
    SCORE_CHART x
       join SCORE_CHART y
          on x.STUDENT_NAME <= y.STUDENT_NAME
group by x.STUDENT_NAME
order by x.STUDENT_NAME

just seem the NO JOINS rule - will re-think

EDIT - running ok now: LIVE FIDDLE HERE

Creating the data

CREATE TABLE "SCORE_CHART"     
(             
  "STUDENT_NAME" NVARCHAR(20),          
  "MARKS" INT    
)  
INSERT INTO SCORE_CHART (STUDENT_NAME, MARKS) 
VALUES 
('STUD1', 95),
('STUD2', 90),
('STUD3', 98)

Using a recursive CTE:

 ;WITH 
    init_cte(row,STUDENT_NAME,MARKS) 
    AS
        (
        SELECT 
        ROW_NUMBER() OVER (ORDER BY STUDENT_NAME),
        STUDENT_NAME,
        MARKS
        FROM SCORE_CHART
        )
    ,MinMax_cte(MinRow,MaxRow)  AS (SELECT MIN(row),MAX(row) FROM init_cte)

    ,recursive_cte (row,STUDENT_NAME,MARKS,RUNNING_MARKS) AS 
      (
         SELECT row,STUDENT_NAME,MARKS,MARKS 
            FROM init_cte 
            WHERE row = (SELECT MinRow FROM  MinMax_cte) 
         UNION ALL
         SELECT Y.row,y.STUDENT_NAME,y.MARKS,x.RUNNING_MARKS + y.MARKS
            FROM recursive_cte x
            INNER JOIN init_cte y
                ON y.row = x.row + 1
            WHERE y.row <= (SELECT [MaxRow] from MinMax_cte)
      )
SELECT * FROM recursive_cte

As mentioned in a comment to you OP there is a similar question HERE ON SO In that question Sam Saffron put forward a very elegant way of doing a running total using UPDATE. This is is applied to your data:

Using the same data created above but with the UPDATE trick:

CREATE TABLE #t ( ROW int, STUDENT_NAME NVARCHAR(20) , MARKS int, MARKS_RUNNING int) 
INSERT INTO #t
SELECT
        ROW_NUMBER() OVER (ORDER BY STUDENT_NAME),
        STUDENT_NAME, 
        MARKS,
        0
FROM SCORE_CHART

DECLARE @total int  
SET @total = 0 
UPDATE #t SET marksrunning = @total, @total = @total + MARKS   

SELECT * FROM #t 

Answer 5

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

Answer 6

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