Muhammad Rizwan Shahid - 3 months ago 103

SQL Question

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