Conditional summation
03:35 12 Jul 2013

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.

sql sql-server sql-server-2008-r2