how to sum value row to row in mysql?

Question

lets say i have data in mysql like this :

date | value
2010 | 1
2011 | 4
2012 | 2
2013 | 3

i want the result like this :

date | value
2010 | 1
2011 | 5
2012 | 7
2013 | 10

I thought this would be simple. I'm trying this:

select tabelA.date, sum(value)
from tabelA
inner join (select date from tabelA group by date) b on tabelA.date > b.date
group by tabelA.date

I feel like I'm missing something obvious. It seems like a simple thing to want to do.

Any ideas?

mysql Ver 15.1 Distrib 5.5.35-MariaDB – Anggoro Setiawan Mar 21 '19 at 09:41 — Anggoro Setiawan, Mar 21 '19 at 09:41

score 1 · Answer 1 · answered Mar 21 '19 at 10:50

The best approach in MySQL 8+ is window functions:

select a.date,
       sum(a.value) over (order by a.date)
from tabelA a
order by a.date;

In older versions, variables are probably the best approach, but they have to be used carefully:

select date, (@csum := @csum + value) as running_sum
from (select a.date, a.value as value
      from tableA a
      order by a.date
     ) a cross join
     (select @csum := 0) params;

In particular, you want the order by in the subquery to be sure that the data is processed in the correct order.

score 0 · Accepted Answer · answered Mar 21 '19 at 09:47

0

SELECT
    b.date,( SELECT sum( a.`value` ) FROM aa a WHERE a.date <= b.date ) value
FROM
    aa b 
GROUP BY
    b.date

answered Mar 21 '19 at 09:47

wl.GIG

266
2
12

score 0 · Answer 3 · answered Mar 21 '19 at 09:49

0

You could use a variable:

SET @cumulative := 0;
SELECT date, (@cumulative := @cumulative+value) as value FROM tabelA

This will just keep track of current sum. It's basically a minor adaptation of this answer to get a row number.

answered Mar 21 '19 at 09:49

apokryfos

34,925
8
65
98

how to sum value row to row in mysql?

3 Answers3