Use CASE Statement With SUM Function In SQL Server

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.AnnualIncomeRa

Solution 1:

Indeed; that case is per row, since you don't have a group; SUM(quantity) is largely meaningless when referring to a single row. If that is the SUM over the entire set, you will have to compute that first into a variable. Otherwise you'll need to think about what group / partition you intended that inner-SUM to apply to.

To give a similar example:

This works:

select 1 as [a], 2 as [b], 3 as [c]

and this works:

select case [a] when 1 then [b] else [c] end from (
  select 1 as [a], 2 as [b], 3 as [c]
) x

but this does not:

select case [a] when 1 then sum([b]) else [c] end from (
  select 1 as [a], 2 as [b], 3 as [c]
) x

likewise, this works:

select sum(case [a] when 1 then [b] else [c] end) from (
  select 1 as [a], 2 as [b], 3 as [c]
) x

but this does not, giving the same error message that you report:

select sum(case [a] when 1 then sum([b]) else [c] end) from (
  select 1 as [a], 2 as [b], 3 as [c]
) x

Solution 2:

not sure what sec is in this case but i would take this and create a derived table

select SUM(t.caseValue) AS ProjectedIncome 
from (select * , case 
      when sec.SecurityTypeID =  2 then (quantity)*(sec.AnnualIncomeRate/100) 
      when sec.SecurityTypeID = 5 then 0 
      when sec.SecurityTypeID = 11 then (quantity)*sec.AnnualIncomeRate    
      else (quantity)*sec.AnnualIncomeRate   
end as caseValue  from Transactions) as t

query above probably will not work off the bat as there is not much information to work with

Answer above explains why the query is not working much better than mine