Ryan Jeremiah Freeman Ryan Jeremiah Freeman - 5 months ago 20
SQL Question

Create a Cumulative Sum Column in MySQL Based On an ID

I have a "simplified" table that looks like this:

player round point
1 1 25
2 1 18
3 1 15
1 2 18
2 2 25
3 2 15


I wanna create a view that calculates pointTot cumulatively based upon plrID

plrID rndID pnt [pointTot]
1 1 25 25
2 1 18 18
3 1 15 15
1 2 18 43
2 2 25 43
3 2 15 30


I've been playing around with different methods for the last few hours.

I would need a variable var based upon the plrID

This is as far as I got without being able to work out how to create a

@psum[@plrID]

set @psum := 0;
select `plrID`, `rndID`, `pnt`, (@psum := @psum + `pnt`) as `pointTot`
from `table`
order by `plrID`;

Answer

You can do this using below query

select t.plrID,t.rndID,t.pnt,sum(t1.pnt) 
from table t 
join table t1 
on t.plrID = t1.plrID 
and t1.rndID<=t.rndID
group by plrID,rndID