时间戳

首页 > 解决方案 > Running total with group by in MySql

php - 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?

标签： phpmysqlpdo

解决方案

group by尝试在Derived Table中使用操作的结果集，然后计算运行余额。

另外，请注意，我为代码清晰（可读性）做了适当的别名，并避免了模棱两可的操作。

尝试以下操作：

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

推荐阅读