0

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?

VengaVenga
  • 680
  • 1
  • 10
  • 13

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you. Sorry that I have not found this nice discussion before bothering you: [create-a-cumulative-sum-column-in-mysql](https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) – VengaVenga Feb 15 '18 at 19:28
  • @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`. – Gordon Linoff Feb 16 '18 at 04:04