Subtraction between 2 SQL queries with group by

Question

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

The first query returns the number of all, let's say houses that I can rent out, while the second returns the ones already rented.

SELECT
    (SELECT COUNT(*) FROM ... GroupBy ...)
      - (SELECT COUNT(*) FROM ... WHERE ...group by) AS Difference

First query result

count() column2    column3
 3       studio     newYork
 6       studio     pekin
 3       apprtment  pekin
 5       house      london
 1       house      lagos

Second query result

count() column2    column3
 2       studio     newYork

I would love to have the first query getting updated depending the the result of the second

count() column2    column3
 1       studio     newYork
 6       studio     pekin
 3       apprtment  pekin
 5       house      london
 1       house      lagos

i did simplify my question ,in reality each select is a join of several tables — Sharp, Dec 25 '16 at 05:01
I see you tried [this answer](http://stackoverflow.com/q/1589070/1542187) as a starting point. Your question is inherently different from that one though due to the fact that the other question wanted a single result, whereas you want many results that each show the difference between two similar sets. — JoshuaTheMiller, Dec 26 '16 at 17:21

score 5 · Answer 1 · answered Dec 25 '16 at 03:28

5

Just use conditional aggregation:

SELECT COUNT(*) -
       SUM(CASE WHEN <some_condition> THEN 1 ELSE 0 END) AS some_count,
       column2,
       column3
FROM yourTable
GROUP BY column2, column3

Here <some_condition> is whatever would have appeared in the WHERE clause of your original second count query.

answered Dec 25 '16 at 03:28

Tim Biegeleisen

502,043
27
286
360

1

I like this response as well because this helped me dig deeper into conditional aggregation and create a summation that shows a deficit by simply setting conditions and either multiplying the number by nothing or -1 (depending on which number should be subtracted from which). Example: `SUM(CASE WHEN FailureType = 'Existing Failure' THEN [value] WHEN FailureType = 'New Failure' THEN -1*[value] END) AS [SummedValues]` – Ryan Harris Jun 05 '20 at 04:03

JoshuaTheMiller · Accepted Answer · 2016-12-26T15:45:15.587

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:

Alias the results of your two subqueries. This will make them easier to use.
Return more columns from your subqueries so that you may join on "something" which would allow for subtraction to only occur on matching rows.
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');

@bob no problem! Please be sure to [accept an answer](http://stackoverflow.com/help/someone-answers) if it solved your problem! (or comment/ask for clarification if it did not). — JoshuaTheMiller, Dec 26 '16 at 20:58

score 0 · Answer 3 · answered Dec 26 '16 at 15:07

0

Select Column2, Column3, Count(*)-(Select Count(*)
                                   From Table2 
                                   Where Table1.Column2=Table2.Column2 and Table1.Column3=Table2.Column3)
From Table1
Group by Column2, Column3

answered Dec 26 '16 at 15:07

Ric_R

145
2
11

Subtraction between 2 SQL queries with group by

3 Answers3

Test Table/Data Setup

Step 1

Step 2