Mysql Running Total Of Payments Grouping By A Column, Share Code

I am trying to create a running total of payments for each portfolio of stocks/shares, where there are multiple stock purchase transactions for a given stock and portfolio, I need

Solution 1:

You can extend your existing query with another sub-query as follows.

select
     @row_n := @row_n + 1 as row_n,
     row_num,
     code,
     portfolio,
     pdate,
     dividend,
     quantity,
     payment,
     balance
     from ( select
     @row_num := @row_num + 1 as row_num,
     code,
     portfolio,
     pdate,
     dividend,
     quantity,
     ceiling(dividend*quantity/100) as payment,
     @balance := ceiling(dividend*quantity/100) + if (@prev_pfl = portfolio,@balance,0) as balance,
     @prev_pfl := portfolio as prev_portfolio
    from ( select
     code,
     portfolio,
     pdate,
     dividend,
     sum(quantity) as quantity 
    from test
    group by portfolio, pdate,code, dividend
    order by portfolio, pdate,code, dividend ) as SubQueryAlias1
    cross join
    ( select @row_num := 0, @balance := 0, @prev_pfl := '' ) as InitVarsAlias1 
    order by portfolio, pdate,code) as SubQueryAlias2
    cross join 
    (select @row_n := 0 ) as InitVarsAlias2 
    order by pdate,portfolio,code,row_num;

and it will provide you with the appropriate output as you requested.

row_n   row_num     code    portfolio       pdate       dividend    quantity    payment     balance
1       2           BLND    AJB_SIPP_CO     2018-05-05  7.52        3286        248         248
2       3           AV.     AJB_SIPP_CO     2018-05-17  15.88       2135        340         588
3       17          AV.     SFT_DEA_CO      2018-05-17  15.88       2318        369         369
4       4           DLG     AJB_SIPP_CO     2018-05-18  9.70        2732        266         854
5       18          DLG     SFT_DEA_CO      2018-05-18  9.70        2789        271         640
6       5           SLA     AJB_SIPP_CO     2018-05-23  13.35       2820        377         1231
7       19          SLA     SFT_DEA_CO      2018-05-23  13.35       3247        434         1074
8       6           PHP     AJB_SIPP_CO     2018-05-27  1.31        6947        92          1323
9       7           LLOY    AJB_SIPP_CO     2018-05-29  2.05        15519       319         1642
10      14          LLOY    SFT_DEA_CL      2018-05-29  2.05        40011       821         821
11      23          LLOY    SFT_ISA_CO      2018-05-29  2.05        7973        164         164
12      1           FCPT    AJB_SIPP_CL     2018-05-31  0.50        223         2           2
13      8           FCPT    AJB_SIPP_CO     2018-05-31  0.50        5837        30          1672
14      9           RLSEB   AJB_SIPP_CO     2018-05-31  1.80        5021        91          1763
15      21          FCPT    SFT_ISA_CL      2018-05-31  0.50        3609        19          19
16      22          RLSEB   SFT_ISA_CL      2018-05-31  1.80        2100        38          57
17      24          FCPT    SFT_ISA_CO      2018-05-31  0.50        5136        26          190
18      25          RLSEB   SFT_ISA_CO      2018-05-31  1.80        2100        38          228
19      10          LGEN    AJB_SIPP_CO     2018-06-08  10.35       3923        407         2170
20      15          LGEN    SFT_DEA_CL      2018-06-08  10.35       10652       1103        1924
21      11          BP.     AJB_SIPP_CO     2018-06-23  7.67        2130        164         2334
22      12          RDSB    AJB_SIPP_CO     2018-06-23  35.02       436         153         2487
23      16          RDSB    SFT_DEA_CL      2018-06-23  35.02       1292        453         2377
24      20          BP.     SFT_DEA_CO      2018-06-23  7.67        446         35          1109
25      13          CNA     AJB_SIPP_CO     2018-06-29  8.40        7512        632         311

Good Luck!