How to subtract values from different tables?

I need to get the result of subtract values from these 3 different tables in SQL Server.

This is my SQL:

SELECT COUNT(A.Id)
FROM Table_A AS A WITH (NOLOCK)
WHERE A.City = 'NewYork'

SELECT COUNT(B.Id)
FROM Table_B AS B WITH (NOLOCK)
WHERE B.City = 'England'

SELECT COUNT(C.Id)
FROM Table_C AS C WITH (NOLOCK)
WHERE C.City = 'Berlin'

Let's say the result of the first query is 9, and the second one is 1, and the third one is 3.

I need to get (9-1-3 = 5). How can I do this?

Solution

You can do this with a CTE or with subqueries.

Using a CTE:

WITH tbla AS (
    SELECT COUNT(A.Id) A
    FROM Table_A AS A
    WHERE A.City = 'NewYork'
),
tblb AS (
    SELECT COUNT(B.Id) B
    FROM Table_B AS B
    WHERE B.City = 'England'
),
tblc AS (
    SELECT COUNT(C.Id) C
    FROM Table_C AS C
    WHERE C.City = 'Berlin'
)
SELECT a.A - b.B - c.C
FROM tbla a
CROSS JOIN tblb b
CROSS JOIN tblc c;

Same thing only with subqueries

SELECT a.A - b.B - c.C
FROM (
    SELECT COUNT(A.Id) A
    FROM Table_A AS A
    WHERE A.City = 'NewYork'
) a
CROSS JOIN (
    SELECT COUNT(B.Id) B
    FROM Table_B AS B
    WHERE B.City = 'England'
) b
CROSS JOIN (
    SELECT COUNT(C.Id) C
    FROM Table_C AS C
    WHERE C.City = 'Berlin'
) c;

Note: I removed the WITH (NOLOCK) hints because in all likelihood they are not needed in this case.