Reputation: 153
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.
Upvotes: 2
Views: 21233
Reputation: 1121
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
Upvotes: 0
Reputation: 1062855
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
Upvotes: 5