Subtraction Between Two SQL Queries

How can I subtract two queries in sql?

You could use sub sql to subtract

SELECT numberofseats - ( select count(reservationseatfk)
    from flight f, reservation r
    where f.departuretime between '11/29/2014' and '11/30/2014' and
          f.reservationflightidfk = f.flightid and r.reservationdeparturetimefk = f.departuretime)
    from plane
    where tail number in
     ( select flighttailnumberfk
       from flight
       where departuretime between '11/29/2014' and '11/30/2014' and
             flightdepartureairportfk = 'jfk' and
             flightarrivalairport = 'mli'
     )

Subtraction between 2 SQL queries with group by

How can I perform a subtraction between the results of 2 queries with
a group by?

You are close with what you have. There are a few changes that would make this work easier, however:

1. Alias the results of your two
   subqueries. This will make them easier to use.
2. Return more columns from your subqueries so that you may join on "something" which would allow for subtraction to only occur on matching rows.
3. Add an alias to your Count(*) statements. Again, this will make them easier to use.

If this image demonstrates what you are looking for:

Then I believe this query will help you out:

SELECT op.ApartmentType,
    op.ApartmentLocation,
    op.TotalOwned,
    ISNULL(tp.TotalOccupied, 0) AS [TotalOccupied],
    op.TotalOwned - ISNULL(tp.TotalOccupied,0) AS [TotalVacant]
FROM
(
    SELECT *,
        COUNT(*) as TotalOwned
    FROM SO_SubtractionQuestion.OwnedProperties
    GROUP BY ApartmentType, ApartmentLocation
) AS op
LEFT JOIN
(
    SELECT *, 
        COUNT(*) as TotalOccupied
    FROM [SO_SubtractionQuestion].[OccupiedProperties]
    GROUP BY ApartmentType, ApartmentLocation
) AS tp
ON op.ApartmentType = tp.ApartmentType 
    AND op.ApartmentLocation = tp.ApartmentLocation

I set this query up similar to your own: it has a select statement with two subqueries and the subqueries have a Count(*) on a grouped query. I also added what I suggested above to it:

• My first subquery is aliased with op (owned properties) and my second is aliased with tp(taken properties).
• I am returning more columns so that I may properly join them in my outer query.
• My Count(*) statements in my subqueries have aliases.

In my outer query, I am then able to join on ApartmentType and ApartmentLocation (look below for the example table/data setup). This creates a result set that is joined on ApartmentType and ApartmentLocation that also contains how many Owned Properties there are (the Count(*) from the first subquery) and how many Occupied Properties there are (the Count(*) from the second subquery). At this point, because I have everything aliased, I am able to do simple subtraction to see how many properties are vacant with op.TotalOwned - ISNULL(tp.TotalOccupied,0) AS [TotalVacant].

I am also using ISNULL to correct for null values. If I did not have this, the result of the subtraction would also be null for rows that did not have a match from the second subquery.

Test Table/Data Setup

To set up the example for yourself, here are the queries to run:

Step 1

For organizational purposes

CREATE SCHEMA SO_SubtractionQuestion;

Step 2

CREATE TABLE SO_SubtractionQuestion.OwnedProperties
(
 ApartmentType varchar(20),
 ApartmentLocation varchar(20)
);

CREATE TABLE SO_SubtractionQuestion.OccupiedProperties
(
 ApartmentType varchar(20),
 ApartmentLocation varchar(20)
);

INSERT INTO [SO_SubtractionQuestion].[OwnedProperties] VALUES ('Studio', 'New York'), ('Studio', 'New York'), ('Studio', 'New York'), ('House', 'New York'), ('House', 'Madison');
INSERT INTO [SO_SubtractionQuestion].[OccupiedProperties] VALUES ('Studio', 'New York'), ('Studio', 'New York');

how do I subtract values from two select statements

Total guess:

select v1.Value1 - v2.Value2 from  

  (Select    Max(Value) as [Value1] from History WHERE Datetime ='2010-1-1 10:10' and tagname ='tag1') as v1   

CROSS JOIN

  (  (Select    Max(Value) as [Value2] from History WHERE Datetime ='2010-1-1 10:12'      and Tagname ='tag2')  as v2)

How to subtract 2 SUM in SQL query

First off, there's a few errors in syntax. You're missing a comma between all the select-elements (between your total_refund and outstanding_amount). You should also check if the SUM() was null, not if the column was null. You can also use COALESCE() if you prefer that. Finally, you need to GROUP BY something useful, like the item_id.

SELECT item_id,
       SUM(final_amount) as total_amount,
       ISNULL(SUM(refund_amount), 0) AS total_refund,
       SUM(final_amount) - ISNULL(SUM(refund_amount), 0) AS outstanding_amount
FROM tabel1 WHERE item_id in ('119688521',
                              '109536343',
                              '99459466',
                              '97126817',
                              '138148320',
                               '107816131')

GROUP BY item_id

• Live demo at http://www.sqlfiddle.com/#!18/94f518/5

---

Related Topics