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 +1as row_n,
     row_num,
     code,
     portfolio,
     pdate,
     dividend,
     quantity,
     payment,
     balance
     from ( select@row_num :=@row_num +1as 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
    groupby portfolio, pdate,code, dividend
    orderby portfolio, pdate,code, dividend ) as SubQueryAlias1
    crossjoin
    ( select@row_num :=0, @balance :=0, @prev_pfl :='' ) as InitVarsAlias1 
    orderby portfolio, pdate,code) as SubQueryAlias2
    crossjoin 
    (select@row_n :=0 ) as InitVarsAlias2 
    orderby pdate,portfolio,code,row_num;

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

row_nrow_numcodeportfoliopdatedividendquantitypaymentbalance12BLNDAJB_SIPP_CO2018-05-05  7.523286        24824823AV.AJB_SIPP_CO2018-05-17  15.882135        340588317AV.SFT_DEA_CO2018-05-17  15.882318        36936944DLGAJB_SIPP_CO2018-05-18  9.702732        266854518DLGSFT_DEA_CO2018-05-18  9.702789        27164065SLAAJB_SIPP_CO2018-05-23  13.352820        3771231719SLASFT_DEA_CO2018-05-23  13.353247        434107486PHPAJB_SIPP_CO2018-05-27  1.316947        92132397LLOYAJB_SIPP_CO2018-05-29  2.051551931916421014LLOYSFT_DEA_CL2018-05-29  2.05400118218211123LLOYSFT_ISA_CO2018-05-29  2.057973        164164121FCPTAJB_SIPP_CL2018-05-31  0.5022322138FCPTAJB_SIPP_CO2018-05-31  0.505837        301672149RLSEBAJB_SIPP_CO2018-05-31  1.805021        9117631521FCPTSFT_ISA_CL2018-05-31  0.503609        19191622RLSEBSFT_ISA_CL2018-05-31  1.802100        38571724FCPTSFT_ISA_CO2018-05-31  0.505136        261901825RLSEBSFT_ISA_CO2018-05-31  1.802100        382281910LGENAJB_SIPP_CO2018-06-08  10.353923        40721702015LGENSFT_DEA_CL2018-06-08  10.35106521103        19242111BP.AJB_SIPP_CO2018-06-23  7.672130        16423342212RDSBAJB_SIPP_CO2018-06-23  35.0243615324872316RDSBSFT_DEA_CL2018-06-23  35.021292        45323772420BP.SFT_DEA_CO2018-06-23  7.674463511092513CNAAJB_SIPP_CO2018-06-29  8.407512        632311

Good Luck!