如何得到每个源的当前余额，然后从所有源的总和中得到它的百分比

6rqinv9w 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(249)

按来源从tbl\U sourceoffunds group中选择source、sum（deposit）、sum（distribute）、sum（deposit）-sum（distribute）、（sum（deposit）-sum（distribute））/（sum（deposit）-sum（distribute））*100作为百分比
它一直在说“#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  |

mysql

来源：https://stackoverflow.com/questions/51611229/how-to-get-the-current-balance-of-each-source-and-then-get-the-percentage-of-it

2条答案

按热度按时间

g6baxovj1#

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

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)

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

os8fio9y2#

试试这个

Select source, deposit, withdrew, 
    deposit - distribute as balance, 
    (deposit - distribute) / (Select sum(deposit - distribute) from tbl_sourceofFunds) * 100 as percentage
From (
SELECT source, 
        SUM(deposit) as deposit, 
        SUM(distribute) as withdrew
FROM tbl_sourceofFunds 
GROUP BY source
) x

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

我来回答

如何得到每个源的当前余额，然后从所有源的总和中得到它的百分比

2条答案

相关问题

热门标签

最新问答