运行total mysql显示行的总和

kzmpq1sx 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(362)

我有下表：

+-------------+-------+-------+
|    date     | agent |  hold |
+-------------+-------+-------+
| 2017-01-01  | A     |   100 |
| 2017-01-01  | B     |   200 |
| 2017-01-02  | C     |   400 |
+-------------+-------+-------+

我要添加运行总数的查询：

SET @runtot:=0;
SELECT
 date,
  SUM(CASE WHEN agent = 'A' THEN hold ELSE 0 END) AS A,
  SUM(CASE WHEN agent = 'B' THEN hold ELSE 0 END) AS B,
 SUM(CASE WHEN agent = 'C' THEN hold ELSE 0 END) AS C,
SUM(CASE WHEN agent = 'D' THEN hold ELSE 0 END) AS D,
sum(hold) as Total,
(@runtot := @runtot + sum(hold)) AS rt
FROM daily_results
group by date

但是我在rt列中得到每行的总和（hold）。我做错什么了？

sql mysql cumulative-sum

来源：https://stackoverflow.com/questions/50045964/running-total-mysql-showing-the-sum-of-row

1条答案

按热度按时间

7xzttuei1#

按照建议使用汇总，或者将agregates移动到子查询，并在主查询中执行运行总计。举个例子

+------------+------------+---------+
| tanggal    | iddowntime | idshift |
+------------+------------+---------+
| 2017-10-03 | MOR01      |       1 |
| 2017-10-03 | NF001      |       2 |
| 2017-10-03 | MOR01      |       2 |
| 2017-10-02 | MOR01      |       2 |
| 2017-10-03 | NF001      |       2 |
| 2017-10-02 | MOR01      |       1 |
| 2017-10-02 | NF001      |       1 |
| 2017-10-02 | NF001      |       1 |
+------------+------------+---------+
8 rows in set (0.00 sec)

select s.*,
        @rt:=@rt + total RunningTotal
from
(
SELECT
  tanggal,
  SUM(CASE WHEN iddowntime = 'mor01' THEN idshift ELSE 0 END) AS A,
  SUM(CASE WHEN iddowntime = 'nf001' THEN idshift ELSE 0 END) AS B,
  #SUM(CASE WHEN agent = 'C' THEN hold ELSE 0 END) AS C,
  #SUM(CASE WHEN agent = 'D' THEN hold ELSE 0 END) AS D,
  sum(idshift) as Total
FROM trans_lhpdtdw
where iddowntime in('mor01','nf001')
group by tanggal 
) s
cross join
(select @rt:=0) rt

+------------+------+------+-------+--------------+
| tanggal    | A    | B    | Total | RunningTotal |
+------------+------+------+-------+--------------+
| 2017-10-02 |    3 |    2 |     5 |            5 |
| 2017-10-03 |    3 |    4 |     7 |           12 |
+------------+------+------+-------+--------------+
2 rows in set (0.01 sec)

赞(0）回复(0）举报 2021-06-23

我来回答

运行total mysql显示行的总和

1条答案

相关问题

热门标签

最新问答