Muhammad Rizwan Shahid Muhammad Rizwan Shahid - 3 months ago 103
SQL Question

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

Answer

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