CODEWITHSUNDEEP
sql-servert-sql
Sharp
Sharp

Reputation: 121

Subtraction between 2 SQL queries with group by

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

Upvotes: 2

Views: 7730

Answers (3)

JoshuaTheMiller
JoshuaTheMiller

Reputation: 3049

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:

Output of the query with the example tables as the data source

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

Upvotes: 2

Ric_R
Ric_R

Reputation: 155

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

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.

Upvotes: 5

Related Questions