MySQL query that computes partial sums

mysql, stored-procedures

Solution

You can do this by joining the table on itself. The SUM will add up all rows up to this row:

select cur.id, sum(prev.val)
from TheTable cur
left join TheTable prev
    on cur.id >= prev.id
group by cur.id

MySQL also allows the use of user variables to calculate this, which is more efficient but considered something of a hack:

select 
     id
,    @running_total := @running_total + val AS RunningTotal
from TheTable

Problem

What query should I execute in MySQL database to get a result containing partial sums of source table? For example when I have table: ``` Id|Val 1 | 1 2 | 2 3 | 3 4 | 4 ``` I'd like to get result like this: ``` Id|Val 1 | 1 2 | 3 # 1+2 3 | 6 # 1+2+3 4 | 10 # 1+2+3+4 ``` Right now I get this result with a stored procedure containing a cursor and while loops. I'd like to find a better way to do this.

Original source