CODEWITHSUNDEEP
phpmysqlpdo
Sonny King
Sonny King

Reputation: 73

Running total with group by in MySql

I'm trying to get a running balance of payouts in a table that will be displayed in a transaction list. The sum of each payout on the left and the running total (accumulated balance) to the right.

Here is my Query:

SELECT *, SUM(payment_sum) as total, @running_total := @running_total
 + payment_sum AS cumulative_sum FROM payments JOIN
(SELECT @running_total := 0)t WHERE id=? 
GROUP BY source, report_date;

The payment_sum and the cumulative_sum (running total) is correct unless I group by source and report_date. Then, the payment_sum is correct but the running total is not. I need to group by source and report_date as a payment from the same source may occur on the same date, so I would like to group all payments from the same source together to not display them twice. This causes the running total to give a false balance. it seems to only total the first group of the sources.

output without source grouped (correct balance):

Source   date      payout  balance
Google   10/18/18    $5.00  $ 5.00
Google   10/18/18    $5.00  $10.00
Amazon   10/18/18    $2.50  $12.50
Amazon   10/18/18    $2.50  $15.00
WebStore 10/18/18    $2.00  $17.00

output with source grouped (incorrect balance):

Source   date      payout  balance
Google   10/18/18    $10.00  $ 5.00
Amazon   10/18/18    $ 5.00  $ 7.50
WebStore 10/18/18    $ 2.00  $ 9.50

Desired output:(source, report_date grouped)

Source   date      payout  balance
Google   10/18/18    $10.00  $10.00
Amazon   10/18/18    $ 5.00  $15.00
WebStore 10/18/18    $ 2.00  $17.00

Any ideas on how to better format my query for the desired results?

Upvotes: 2

Views: 2462

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Try using the result-set of group by operation in a Derived Table, and then compute the running balance.

Also, note that I have done proper Aliasing for code clarity (readability), and avoid ambiguous operations.

Try the following:

SELECT dt.*, 
       @running_total := @running_total + dt.total AS cumulative_sum 
FROM (
      SELECT t1.*, 
             SUM(t1.payment_sum) as total 
      FROM payments AS t1 
      WHERE t1.id = ? 
      GROUP BY t1.source, t1.report_date 
     ) AS dt 
CROSS JOIN (SELECT @running_total := 0) AS t2

Upvotes: 1

Related Questions