Conditional summation
I am working on SQL Server 2008 R2. I am trying to get the sum.
This is my query
select
sum(
case
when sec.SecurityTypeID = 2 then sum(quantity)*(sec.AnnualIncomeRate/100)
when sec.SecurityTypeID = 5 then 0
when sec.SecurityTypeID = 11 then sum(quantity)*sec.AnnualIncomeRate
else sum(quantity)*sec.AnnualIncomeRate
end
) AS ProjectedIncome
from Transactions as t
When I execute it give me following error.
Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a sub query.
I know I am using sum function with case clause. But I need to find sum with this case expression.