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

Question

I am trying to add the result of the total of some amount and substract it to the total but i see the following error:

Imagine something like this

First Subquery : 1 3 5 7 Second Subquery : 2 4 6

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

This is my query but as I said i see the following error:

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

How could I convert the query not to see the error :

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

Many thanks in advance

Answer 1

You can construct a query in a way to turn additions into subtractions for 'Negative' values, like this:

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

Now a single SUM is used, with the sign of the addition controlled by the CASE expression.