mysql - 如何获取每个来源的当前余额，然后获取其占所有来源总和的百分比

Question

选择来源，SUM(存款)，SUM(分发)，SUM(存款)-SUM(分发)，(SUM(存款)-SUM(分发))/(SUM(SUM(存款)-SUM(分发)) ) * 100 作为百分比来自 tbl_sourceofFunds按来源分组

它一直显示“#1111 - 组功能使用无效”

source |    deposit    |    withdraw | 
--------------------------------------
A      |    300,000.00 |             |
B      |    300,000.00 |             |
C      |    220,000.00 |             |
A      |               |   53,300.00 |
A      |     20,000.00 |             |
B      |               |    3,700.00 |
C      |               |    5,100.00 |

我的意思是得到:

source | sum.deposit   |sum.withdraw |   balance   | percentage  |
------------------------------------------------------------------
A      |    320,000.00 |      53,300 |  266,700.00 |  34.284612  |
B      |    300,000.00 |       3,700 |  296,300.00 |  38.089729  |
C      |    220,000.00 |       5,100 |  214,900.00 |  34.284612  |

Answer 1

您可以通过将计算包含在子查询中来获取整个表的总计

DROP TABLE IF EXISTS sourcefunds;
CREATE TABLE sourcefunds(source VARCHAR(1),    deposit    DECImal (10,2),    distribute decimal(10,2)); 
insert into sourcefunds values
('A'  ,        320000.00 ,  null           ),
('B'  ,        300000.00 ,  null           ),
('C'  ,        220000.00 ,  null           ),
('A'  ,             null  ,   53300.00 ),
('A'  ,         20000.00 ,  null           ),
('B'  ,             null  ,    3700.00 ),
('C'  ,             null  ,    5100.00 );

SELECT source, SUM(deposit), SUM(distribute), SUM(deposit)-SUM(distribute),
    (SUM(deposit)-SUM(distribute)) / (select sum(deposit) - sum(distribute) from sourcefunds) * 100 as percentage
        FROM sourcefunds
        GROUP BY source;

+--------+--------------+-----------------+------------------------------+------------+
| source | SUM(deposit) | SUM(distribute) | SUM(deposit)-SUM(distribute) | percentage |
+--------+--------------+-----------------+------------------------------+------------+
| A      |    340000.00 |        53300.00 |                    286700.00 |  35.931821 |
| B      |    300000.00 |         3700.00 |                    296300.00 |  37.134979 |
| C      |    220000.00 |         5100.00 |                    214900.00 |  26.933200 |
+--------+--------------+-----------------+------------------------------+------------+
3 rows in set (0.00 sec)

如果您想要总计使用汇总

SELECT source, SUM(deposit), SUM(distribute), SUM(deposit)-SUM(distribute),
    (SUM(deposit)-SUM(distribute)) / (select sum(deposit) - sum(distribute) from sourcefunds) * 100 as percentage
        FROM sourcefunds
        GROUP BY source with rollup;

+--------+--------------+-----------------+------------------------------+------------+
| source | SUM(deposit) | SUM(distribute) | SUM(deposit)-SUM(distribute) | percentage |
+--------+--------------+-----------------+------------------------------+------------+
| A      |    340000.00 |        53300.00 |                    286700.00 |  35.931821 |
| B      |    300000.00 |         3700.00 |                    296300.00 |  37.134979 |
| C      |    220000.00 |         5100.00 |                    214900.00 |  26.933200 |
| NULL   |    860000.00 |        62100.00 |                    797900.00 | 100.000000 |
+--------+--------------+-----------------+------------------------------+------------+
4 rows in set (0.00 sec)