total sum from previous column plus current column in SQL

Question

Say I have the table below:

 Id     Grade 1    Grade 2    Grade 3
  1       1           1         1  
  2       5           0         0
  3       3           1         5

I want the result as exactly as follows:

 Id     Grade 1    Grade 2    Grade 3      Total
  1       1           1         1            3
  2       5           0         0            8
  3       3           1         5            17

Total should be the sum from the current column + the previous total.

Is that possible in MYSQL?

Interesting question, but at least provide what have you tried... :) — Tomas Pastircak, Apr 08 '14 at 06:19
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Mikael Eriksson, Apr 08 '14 at 07:47
Is it `sql-server` as the tags indicate, or **MySQL** as you state in your question?? Please clarify !! — marc_s, Apr 08 '14 at 07:47

fancyPants · Accepted Answer · 2014-04-08T07:18:52.870

5

select
t.*,
@rolling_sum := @rolling_sum + `Grade 1` + `Grade 2` + `Grade 3` AS Total
from
Table1 t
, (select @rolling_sum := 0) var_init
order by id

see it working live in an sqlfiddle

Another version:

select t.*,
(select sum([Grade 1] + [Grade 2] + [Grade 3]) from Table1 sub_t where sub_t.id <= t.id)
from Table1 t
order by id

edited Apr 08 '14 at 07:18

answered Apr 08 '14 at 06:20

fancyPants

50,732
33
89
96

1

got na errors: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ':'. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ':'. – user2857350 Apr 08 '14 at 06:26
Maybe it's the frontend you're using? I can't reproduce the problem. Have you copy&pasted or rewritten? – fancyPants Apr 08 '14 at 06:29
Or maybe have a try with two statements instead of one: set @rolling_sum = 0; select t.*, @rolling_sum := @rolling_sum + `Grade 1` + `Grade 2` + `Grade 3` AS Total from Table1 t order by id; – fancyPants Apr 08 '14 at 06:32
Any difference in the error message? Have you copy&pasted? – fancyPants Apr 08 '14 at 06:40
Posted another version in my answer. But don't try it in sqlfiddle. This is for SQL Server, not MySQL. – fancyPants Apr 08 '14 at 06:53
The 1st total is null – user2857350 Apr 08 '14 at 07:12
@user2857350 Please have a try again. All is fixed now and tested on SQL Server too. – fancyPants Apr 08 '14 at 08:41

score 0 · Answer 2 · answered Apr 08 '14 at 06:22

0

Try this

SELECT A.*, (@runtot := @runtot + `Grade 1` + `Grade 2` + `Grade 3`) AS Total
FROM Table1 A
,(SELECT @runtot:=0) c

Fiddle Demo

answered Apr 08 '14 at 06:22

Vignesh Kumar A

27,863
13
63
115

score 0 · Answer 3 · answered Apr 09 '14 at 05:57

I think the easiest method is summing values with a subselect statement as follows as described at tutorial titled SQL running total sample

I also used a CTE statement to sum of individual grades as the sum of each line. Then I used the sum in the sub-select statement

;with cte as (
    select
        id,
        grade1,
        grade2,
        grade3,
        isnull(grade1,0) + isnull(grade2,0) + isnull(grade3,0) as linetotal
    from grade
)
select *, total = (select sum(ss.linetotal) from cte ss where ss.id <= cte.id)
from cte

total sum from previous column plus current column in SQL

3 Answers3