Use CASE statement with SUM function in SQL Server

sql, sql-server, sql-server-2008-r2

Solution

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

Problem

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

Original source