Katherine99 Katherine99 - 6 months ago 27
SQL Question

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

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

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.

Comments