mysql - 如何计算总和列的百分比？

Question

我想计算下表中累计金额列的百分比

id  amount cumulative_amount
1   10000  10000
2   15000  25000
3    5000  30000
4   10000  40000

我想要输出为

id  amount cumulative_amount  percentage
1   10000  10000              100%
2   15000  25000              166.67% (i.e cumulative_amount*100/amount)
3    5000  30000              600%
4   10000  40000              400%

那么如何在mysql中写查询来实现上面的输出

我在下面写了查询

SELECT amount,SUM(amount) OVER(ORDER BY id) AS cumulative_amount,
concat(round((cumulative_amount/amount * 100 ),2),'%') AS percentage
FROM mytable ORDER BY id DESC LIMIT 1;

但我收到 mysql 错误，指出字段列表中的未知列“cumulative_amount”，所以我将如何实现我的输出请帮助我

提前致谢

Answer 1

你可以试试下面-

SELECT amount,SUM(amount) OVER(ORDER BY id) AS cumulative_amount,
concat(round((SUM(amount) OVER(ORDER BY id))/amount * 100 ),2),'%') AS percentage
FROM mytable ORDER BY id DESC LIMIT 1