CODEWITHSUNDEEP
mysqlsql
Anggoro Setiawan
Anggoro Setiawan

Reputation: 23

how to sum value row to row in mysql?

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?

Upvotes: 2

Views: 57

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 1

apokryfos
apokryfos

Reputation: 40663

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.

Upvotes: 0

wl.GIG
wl.GIG

Reputation: 316

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

Upvotes: 0

Related Questions