Cumulative total in MS Sql server

Question

Possible duplicate:
Calculate the total in SqlServer

I need to get the cumulative (current) sum of a column in ms-sql server. That is, if there is a column with the name "Signs", then the corresponding row for each cumulative sum will be the sum of the current and previous rows. Can we achieve results without using unions? Because my request is quite large.

I 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 is easy to write, for example:

 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

The same query has been supported since 2012. In older versions, there are several approaches. See http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

Answer 2

try the following:

you can get the cumulative amount by simply adding the same table

 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 3

You said you didn’t join, but how to 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 the index on student_name, the speed should be fine!

Answer 4

Check the query for a 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 5

Use a recursive CTE to achieve this.

Answer 6

JUSt makes the connection, it does not seem to guarantee order, but approaches the final answer:

 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 seems NO JOINS rule - change your mind

EDIT - now runs fine: LIVE FIDDLE HERE

Data creation

 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 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 the commentary to you, the OP has a similar question HERE SO. In this question, Sam Shaffron suggested a very elegant way to do the general work using UPDATE . This applies to your data:

Using the same data that was 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