how to get the sum of the rows before the current row in MYSQL?

Question

Possible Duplicate:
MySQL query that computes partial sums

ok, the question is not clear at all so the example would be: i have a table like this:

id     amount

1       1000
2       2500
3       5000

and i need to select from it the following data:

id     oamount     total

1       1000      1000
2       2500      3500
3       5000      8500

i tried this but it is not correct:

    select *,sum(oamount) from `table` 
group by id;

and i can't figure it out

score 7 · Accepted Answer · edited May 23 '17 at 12:07

I've answered a very similar where they were trying to get cash flow balances for beginning / ending of each day... Found here

Yours would be very similar... Prequery the data in the final order you want it (ie: starting ID would be the first of the result set), then apply what you want with MySQL Variables

select
      PreAgg.ID,
      PreAgg.Amount,
      @PrevBal := @PrevBal + PreAgg.Amount as Total
   from 
      ( select
              YT.id,
              YT.amount
           from
              YourTable YT
           order by
              YT.id ) as PreAgg,
      ( select @PrevBal := 0.00 ) as SqlVars

I've actually kept the pre-aggregate as a query... in case you wanted to actually DO some aggregations like based on transactions on a daily basis, or applied some other WHERE clause. This would allow flexibility to ensure your final output order was prepared BEFORE starting your @PrevBal accumulation.

I was thinking about some PL/SQL with cursor and loop, but this is cool ;) — rkosegi, Jan 12 '12 at 19:20

score 0 · Answer 2 · answered Jan 12 '12 at 19:08

0

Try the following:

select 
  *,
  (select sum(oamount) from `table` WHERE id <= t.Id) AS total
from `table` AS t

answered Jan 12 '12 at 19:08

Oleg Dok

21,109
4
45
54

2

replace < with <= and pray for ids to be unique :) – Benoit Jan 12 '12 at 19:09
Although it could work, depending on the size of the data, could become painful, 5 record would have to count 5 + 4 + 3 + 2 + 1 instances in the subquery... try that again with 1000 entries... – DRapp Jan 12 '12 at 19:12
@DRapp Yep, performance killer. Its a fast'n'simple decision. Performance-effective will be slightly different – Oleg Dok Jan 12 '12 at 19:14

score 0 · Answer 3 · answered Jan 12 '12 at 19:11

0

 SELECT id,amount,@total := @total + amount 
 FROM `table`, (SELECT @total := 0) as dummy 
 ORDER BY id;

Note that this doesn't work when using GROUP BY clauses / aggregates.

answered Jan 12 '12 at 19:11

Wrikken

69,272
8
97
136

score 0 · Answer 4 · answered Jan 12 '12 at 19:19

0

try this:

SET @res:=0;
SELECT
   t.a,
   t.b,
   (@res := @res + t.b) AS summ
FROM
(SELECT first AS a, second AS b FROM table GROUP BY a ORDER BY a) AS t

answered Jan 12 '12 at 19:19

vrtx64

91
3

how to get the sum of the rows before the current row in MYSQL?

4 Answers4