sql-server - T-SQL : Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Question

我正在尝试将总计的结果相加并将其减去总计，但我看到以下错误:

想象一下这样的事情

第一个子查询:1 3 5 7第二个子查询:2 4 6

总计:(1+3+5+7) - (2+4+6) = 4

这是我的查询，但正如我所说，我看到以下错误:

     Select SUM  ((
 (select SUM (amount)  FROM  transfer tr1 
    where transfer_type = 'Positive'  group by transfer_id)
EXCEPT
(SELECT SUM (amount) from transfer tr2 
    where  transfer_type = 'Negative'  group by transfer_id)))

我怎样才能转换查询而不看到错误:

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

提前致谢

Answer 1

您可以构建一个查询，将加法转换为 'Negative' 值的减法，如下所示:

SELECT
    transfer_id
,   SUM (
        CASE 'transfer_type'
            WHEN 'Positive' THEN amount
            WHEN 'Negative' THEN -amount
            ELSE NULL
        END
    ) AS total
FROM transfer
GROUP BY transfer_id

现在使用单个 SUM，加法符号由 CASE 表达式控制。