More complex cumulative sum column in mysql

Question

I read Create a Cumulative Sum Column in MySQL, and tried to adapt it to what I'm doing, but I can't seem to get it right.

The table:

Id (primary key)
AcctId
CommodId
Date
PnL

There is a unique index which contains AcctId, CommodId, Date. I want to get a cumulative total grouped by date.

This query

select c.date
   , c.pnl
   ,(@cum := @cum + c.pnl) as "cum_pnl"
   from commoddata c join (select @cum := 0) r
   where
   c.acctid = 2
   and
   c.date >= "2011-01-01"
   and
   c.date <= "2011-01-31"
   order by c.date

will correctly calculate the running total for all records, showing data in the format

date        pnl       cum_pnl
========    ======    =======
2011-01-01       1          1
2011-01-01       1          2
2011-01-01       1          3
2011-01-01       1          4
2011-01-02       1          5
2011-01-02       1          6
...

(there can be many records per date). What I want is

date        cum_pnl
========    =======
2011-01-01       4
2011-01-02       6
...

But nothing I've tried works. TIA.

score 2 · Accepted Answer · edited Jan 03 '13 at 15:40

2

Alternately I think you can replace all your pnl with sum(pnl), and let your @cum run across those. I think it would look like this:

select c.date
   ,SUM(c.pnl)
   ,(@cum := @cum + SUM(c.pnl)) as "cum_pnl"
   from commoddata c join (select @cum := 0) r
   where
   c.acctid = 2 and c.date >= "2011-01-01" and c.date <= "2011-01-31"
   order by c.date
   GROUP BY c.date

I'm just trying to figure out if SQL will give you grief over selecting cum_pnl when it is not a group by expression... maybe you can try grouping by it as well?

EDIT New Idea, if you're really not averse to nested queries, replace commoddata with a summed grouped query

select c.date
   ,c.pnl
   ,(@cum := @cum + c.pnl) as "cum_pnl"
   from 
       (SELECT date, sum(pnl) as pnl FROM commoddata WHERE [conditions] GROUP BY date) c 
       join (select @cum := 0) r
   order by c.date

edited Jan 03 '13 at 15:40

Andrew Hedges

21,688
16
67
79

answered Apr 19 '11 at 03:28

Brad

603
4
12

Yeah, I'd tried that one before, it produces numbers that I can't even understand -- nothing like the actual totals. (This was my response to the pre-edit posting.) – davej Apr 19 '11 at 03:34
(New idea response:) Ok, tried that, and got 0 records back. (Had to add "acctid" to the inner select, otherwise it choked on the "where c.acctid = 2" in the outer portion). I appreciate your efforts. – davej Apr 19 '11 at 03:40
Shouldn't need to add it to the select to use it as a condition... you weren't selecting it before. If you left the conditions as c.foo you'll need to change the inner select, [FROM commoddata WHERE] -> [FROM commoddata c WHERE]. If it still doesn't work, try running just the inner query and tweaking it until it returns good (date, sum_pnl) pairs. – Brad Apr 19 '11 at 03:44
DING DING DING! We have a winner! Your edited version (with the where condition in the inner select) works! Many thanks! – davej Apr 19 '11 at 03:53

score 0 · Answer 2 · answered Apr 19 '11 at 03:17

0

Probably not the best way, but you can SELECT Date, MAX(Cum_PnL) FROM (existing_query_here) GROUP BY Date

answered Apr 19 '11 at 03:17

Brad

603
4
12

That would work if all the Cum_PnL's were positive, but they're not; what I need is the last Cum_PnL per day, but I can't figure out how to do that -- I can't find a "last" function. Thanks, tho. – davej Apr 19 '11 at 03:27
A truly terrible idea would be to have a second accumulator X that simply accumulated 1, and select max of (a trillion times X, plus Cum_PnL), and then mod to the nearest trillion later? – Brad Apr 19 '11 at 03:32

More complex cumulative sum column in mysql

2 Answers2

Linked