“无法对包含聚合或子查询的表达式执行聚合函数”

Question

我知道这个情节有问题。但这些问题都不能解决我的具体问题。

这是我的代码

         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

这是给我这样的桌子

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

我想像其他单词一样 this.in 结果，我想得到该表结果的总和。

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

当我将我的代码更改为

            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      

我收到此错误

无法对包含聚合或子查询的表达式执行聚合函数。

有什么帮助吗？

Answer 1

您可以将聚合应用为子集。

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