根据mysql中的另一列获取百分比列
16lz
2021-01-22
I have a payment table looks like below:
我的付款表如下所示:
+--------------+---------+
| Type | Amount |
+--------------+---------+
| General | 500 |
| General | 700 |
| Assets | 450 |
| Business | 270 |
| General | 70 |
| Assets | 350 |
+--------------+---------+
I want the sum and percentage group by type.
我希望按类型划分总和和百分比。
My query is --
我的疑问是 -
SELECT fld_type, SUM(fld_amount) FROM tbl_payment group by fld_type
+--------------+---------+
| Type | Amount |
+--------------+---------+
| General | 1270 |
| Assets | 800 |
| Business | 270 |
+--------------+---------+
I just getting the sum but can't calculate percentage by sum of amount in this case 2340, and calulate with each group item, such as for general -
我只是得到了总和,但是不能用这个案例中的金额总和来计算百分比2340,并且用每个组项目计算,例如一般 -
1270*100/2340 = 56.27
1270 * 100/2340 = 56.27
like this --
喜欢这个 -
+--------------+---------+----------+
| Type | Amount | Percent% |
+--------------+---------+----------+
| General | 1270 | 54.27 |
| Assets | 800 | 34.19 |
| Business | 270 | 11.54 |
+--------------+---------+----------+
Any help is appreciated.
任何帮助表示赞赏。
2 个解决方案
#1
1
To get the percentage for each group you could do it like
要获得每个组的百分比,您可以这样做
select fld_type,
sum(fld_amount),
(sum(fld_amount) / t.total) * 100 percent
from tbl_payment
cross join (
select sum(fld_amount) total from tbl_payment
) t
group by fld_type
Demo