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: enter image description here

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.

I can't see your expected result (imgur blocked at work) - could you post a text based example? — Michael A
– Michael A, Commented Jul 26, 2012 at 7:11
for the first row cumulative sum should be 95, 185 for second and 283 for third. — TechDo
– TechDo, Commented Jul 26, 2012 at 7:24

Madhivanan · Accepted Answer · 2012-07-26 07:11:50Z

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

Joe G Joseph · Accepted Answer · 2012-07-26 07:49:54Z

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

Igor Borisenko · Accepted Answer · 2012-07-26 07:54:35Z

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!

Thanks for the reply, the query works fine for the given data. Issue is that my select contains 3-4 table joins, so to implement CROSS APPLY I have to put the whole stuff in inner query and seems very big. Thanks once again for the help.

AnandPhadke · Accepted Answer · 2012-07-26 09:10:04Z

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

AnandPhadke · Accepted Answer · 2012-07-26 07:14:37Z

0

Use Recursive CTE to achive this.

answered Jul 26, 2012 at 7:14

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

3 Comments

TechDo Over a year ago

Can u please post the query for this table structure?

whytheq Over a year ago

I've added a recursive CTE full example to my answer ... it's not the easiest structure to understand, (but the pain gets a little less every time I write one!)

AnandPhadke Over a year ago

Check the sql query with Recursive CTE.Just now I ahve uploaded.

Community · Accepted Answer · 2017-05-23 12:19:42Z

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

Collectives™ on Stack Overflow

Cumulative Total in MS Sql server [duplicate]

6 Answers 6

Comments

1 Comment

1 Comment

1 Comment

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

1 Comment

1 Comment

3 Comments

Comments

Linked

Related