I'm trying to divide two values to get the percentage. I need the 'VA' AUM / 'TOTAL' AUM to get the percentage.

Below are the two different methods that I have tried, but they are not providing the percentage. The first method only provides 'NULL'.

How do I take the product of 'VA' and the AUM field value and divide by the the product of 'TOTAL' and it's AUM field value to get the percentage? The Product is a column and the AUM is a separate column.

 SELECT 
'VA % OF BDAUM' = CASE WHEN PRODUCT = 'VA' THEN AUM END / 
                    CASE WHEN PRODUCT = 'TOTAL' THEN AUM END

--(SUM(CASE WHEN BD.PRODUCT = 'VA' THEN BD.AUM ELSE 0 END) / 
--NULLIF(SUM(CASE WHEN BD.PRODUCT = 'TOTAL' THEN BD.AUM ELSE 0 END),0))

FROM #base
WHERE AcctType = 'BD AUM'

CodePudding user response：

Use a subquery for either the numerator or the denominator:

select 
'VA % OF BDAUM' = AUM
                  / (select AUM
                     from #base
                     where AcctType = 'BD AUM'
                       and PRODUCT = 'TOTAL')

FROM #base
WHERE AcctType = 'BD AUM'
and PRODUCT = 'VA'

This is because they are not on the same row.

You may still get zero if the AUM column is an integer. You will also get divide by zero if the 'total' AUM is zero. I assume you know how to deal with those cases.

You can also achieve the same result (same performance) with a self join.