SQL Server Sum特定事务类型金额

Question

我有一个临时表(#general)，其中包含账号、交易类型、金额信息，如下所示

AccountNumber   Tran_Type   Credit
96551AQ6        TSCNYAEUQF  0.0000000
96551AQ6        TSCNYAEUQF  719.0600000
96551AQ6        TSCNYAEUQF  0.0000000
96551AQ6        TSCNYAEUQF  49.4200000
96551AQ6        TSCNYAEUQF  299.0200000
96551AQ6        TSCNYAEUQF  29.6500000

我正在尝试按账号计算特定交易类型的总和在总金额中的百分比。如下所示：

select accountnumber,
((select sum(Credit) from #general where Tran_type='INTTRANSINBOUND')*100.0/
(select sum(Credit) from #general)) as percentage
 
  from #general group by accountnumber

然而，我得到了所有账号的相同百分比，我想这是所有账号的百分比。

我做错了什么？

Answer 1

使用条件聚合尝试以下操作

select accountnumber,
(sum(case when Tran_type='INTTRANSINBOUND' Credit)*100.0)/sum(Credit) as percentage
from #general 
group by accountnumber