I have a temp table (#general) that contains Account number, transaction type, Amount info as below
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
I am trying to calculate the percentage of sum of a specific transaction type in total amount by account number.. as in below:
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
However, i got same percentage for all account numbers, which is percentage of all, i presume.
What do i do wrong?
Advertisement
Answer
Try the below using conditional aggregation
select accountnumber, (sum(case when Tran_type='INTTRANSINBOUND' Credit)*100.0)/sum(Credit) as percentage from #general group by accountnumber