“Cannot perform an aggregate function on an expression containing an aggregate or a subquery”

Question

I know that there are questions with this plot.But none of that questions does not solve my specific problem.

Here is my code

         SELECT [fn_CAmount](SUM(IIF(Id = 2 ,Amount,0)),CurrencyId,@toCurrency)  AS TAmount,
                [fn_CAmount](SUM(IIF(Id = 5 ,Amount,0)),CurrencyId,@toCurrency)  AS BAmount,
                [fn_CAmount](SUM(IIF(Id = 4 ,Amount,0)),CurrencyId,@toCurrency)  AS TAmountW,
                FromCurrencyId AS FromID
                @toCurrencyId  AS ToId
                FROM [dbo].[fn_DReport]() 
                WHERE
                OperationTypeId IN(2,4,5)  
                GROUP BY CurrencyId

this is give me table like this

                TAmont   Bamount  TAmountW  FromID ToId 
                --------|-------|---------|------|------
                   10   |5      |     8   |USD   |USD
                    5   |2      |     2   |EUR   |USD
                        |       |         |      |

I want to result like this.in other words i want to get sum of that table results.

                TAmont   Bamount  TAmountW  FromID ToId 
                --------|-------|---------|------|------
                   15   |7      |   10    |USD   |USD

And when i change my code to

            SELECT 
            SUM([fn_CAmount](SUM(IIF(Id = 2 ,Amount,0)),CurrencyId,@toCurrency))  AS TAmount,
            SUM([fn_CAmount](SUM(IIF(Id = 5 ,Amount,0)),CurrencyId,@toCurrency))  AS BAmount,
            SUM([fn_CAmount](SUM(IIF(Id = 4 ,Amount,0)),CurrencyId,@toCurrency))  AS TAmountW      

I am getting this error

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Any help?

Answer 1

You could apply aggregation as subset.

select sum(TAmount) TAmountSum,sum(BAmount) BAmountSum,sum(TAmountW) TAmountWSum, @toCurrencyId CurrencyId from (
SELECT [fn_CAmount](SUM(IIF(Id = 2 ,Amount,0)),CurrencyId,@toCurrency)  AS TAmount,
    [fn_CAmount](SUM(IIF(Id = 5 ,Amount,0)),CurrencyId,@toCurrency)  AS BAmount,
    [fn_CAmount](SUM(IIF(Id = 4 ,Amount,0)),CurrencyId,@toCurrency)  AS TAmountW,
    FromCurrencyId AS FromID
    FROM [dbo].[fn_DReport]() 
    WHERE
    OperationTypeId IN(2,4,5)  
    GROUP BY CurrencyId
) Results