Mysql aggregated sum line by line [duplicate]

Question

Table t1:

date       profit
2011-01-01  100
2011-01-02  -50
2011-01-03   25

I'd like to get an output like:

date       aggregated profit
2011-01-01  100
2011-01-02   50
2011-01-03   75

Any suggestion for an efficient MySQL query?

Which MySQL version?

jarlh
– jarlh

2018-02-14 13:17:15 +00:00
Commented Feb 14, 2018 at 13:17 — jarlh
– jarlh, Commented Feb 14, 2018 at 13:17

Gordon Linoff · Accepted Answer · 2018-02-14 13:18:07Z

0

You are looking for a cumulative sum. The most efficient method uses variables:

select t.*, (@sum := @sum + profit) as cumulative_profit
from (select t.*
      from t
      order by date
     ) t cross join
     (select @sum := 0) params;

Note that in earlier versions of MySQL, the subquery is not necessary. Somewhere around 5.7, the subquery became needed for the variable to respect the ordering.

answered Feb 14, 2018 at 13:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VengaVenga Over a year ago

Thank you. Sorry that I have not found this nice discussion before bothering you: create-a-cumulative-sum-column-in-mysql

Gordon Linoff Over a year ago

@VengaVenga . . . Unfortunately, the accepted answer to that question doesn't work in the most recent versions of MySQL because you need a subquery for the join.

Collectives™ on Stack Overflow

Mysql aggregated sum line by line [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related