SQL Server Sum specific transaction type amounts

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?

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

Advertisement

Answer