Subtract the result of one query from another

dbfromnewjersey
dbfromnewjersey used Ask the Experts™
on
I have 2 queries which are listed below.

I need a query that will subtract the result of the second query from the result of the first

Query 1:

SELECT sum(AMOUNT_FIELD) AS AMOUNT_TOTAL
FROM Source;

Query 2:

SELECT sum(AMOUNT_FIELD) AS AMOUNT_TOTAL
FROM Target;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
When I need to guess, you mean a scalar subtract on the number values:

SELECT S.Sum - T.Sum AS Delta
FROM   (   SELECT 1 AS ID ,
                  SUM(AmountColumn) AS Sum
           FROM   Source ) S
       INNER JOIN (   SELECT 1 AS ID ,
                             SUM(AmountColumn) AS Sum
                      FROM   Target ) T ON S.ID = T.ID;

Open in new window


p.s. the subtract operator on sets is EXCEPT, but this operator is not available as the UNION, which is the plus operator on sets.

Author

Commented:
Thank you.  Works great.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
or
SELECT  SUM(UQ.Amount) AS Difference
FROM (
SELECT SUM(AmountColumn) from Source
UNION 
SELECT -1 * SUM(AmountColumn) FROM Target
) as UQ

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial