mysql - Php Script to Calculate Cumulative Totals for Accounts

Question

I have the following set of data in a MySQL database table

 CusName   CusID    Quantity   Date
 Test1      1        2         2017-01-01
 Test1      1        1.5       2017-01-10
 Test1      1        5         2017-01-25
 Test2      2        0.5       2017-01-02
 Test2      2        3         2017-01-05
 Test3      3        10        2017-01-01
 Test4      4        4         2017-01-18
 Test4      4        6         2017-01-19

I need to create a php script to calculate cumulative totals for the quantity for each customer as follows

 CusName   CusID    Quantity   QuantityTotal  Date
 Test1      1        2          2             2017-01-01
 Test1      1        1.5        3.5           2017-01-10
 Test1      1        5          8.5           2017-01-25
 Test2      2        0.5        0.5           2017-01-02
 Test2      2        3          3.5           2017-01-05
 Test3      3        10         10            2017-01-01
 Test4      4        4          4             2017-01-18
 Test4      4        6          10            2017-01-19

Answer 1

One approach is to use a correlated subquery:

SELECT
    CusName,
    CusID,
    Quantity,
    (SELECT SUM(t2.Quantity) FROM yourTable t2
     WHERE t2.CusID = t1.CusID AND t2.Date <= t1.Date) QuantityTotal,
    Date
FROM yourTable t1
ORDER BY
    CusID,
    Date;

Demo