Cumulative Total in MS Sql server

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, Jul 26 '12 at 07:11
for the first row cumulative sum should be 95, 185 for second and 283 for third. — TechDo, Jul 26 '12 at 07:24

score 5 · Answer 1 · answered Jul 26 '12 at 07:11

5

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

answered Jul 26 '12 at 07:11

Madhivanan

13,470
1
24
29

score 2 · Answer 2 · answered Jul 26 '12 at 07:49

2

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

answered Jul 26 '12 at 07:49

Joe G Joseph

23,518
5
56
58

This will be very slow. Refer the link I posted – Madhivanan Jul 26 '12 at 08:04

score 1 · Answer 3 · edited Jul 26 '12 at 07:54

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!

edited Jul 26 '12 at 07:54

Igor Borisenko

3,806
3
34
49

answered Jul 26 '12 at 07:36

Christoph Staudinger

75
1
9

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. – TechDo Jul 26 '12 at 07:55

score 1 · Answer 4 · answered Jul 26 '12 at 09:10

1

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

answered Jul 26 '12 at 09:10

AnandPhadke

13,160
5
26
33

@techdo +1 is the usual response – whytheq Jul 28 '12 at 17:49

score 0 · Answer 5 · answered Jul 26 '12 at 07:14

0

Use Recursive CTE to achive this.

answered Jul 26 '12 at 07:14

AnandPhadke

13,160
5
26
33

Can u please post the query for this table structure? – TechDo Jul 26 '12 at 07:29
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!) – whytheq Jul 26 '12 at 08:24
Check the sql query with Recursive CTE.Just now I ahve uploaded. – AnandPhadke Jul 26 '12 at 09:13

score 0 · Answer 6 · edited May 23 '17 at 12:19

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

Cumulative Total in MS Sql server

6 Answers6

Linked

Related