How to subtract values from different tables?-CodePudding

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?

CodePudding user response：

You can do this with a CTE or with subqueries.

Using a CTE:

WITH tbla AS (
    SELECT COUNT(A.Id)
    FROM Table_A AS A
    WHERE A.City = 'NewYork'
),
tblb AS (
    SELECT COUNT(B.Id)
    FROM Table_B AS B
    WHERE B.City = 'England'
),
tblc AS (
    SELECT COUNT(C.Id)
    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)
    FROM Table_A AS A
    WHERE A.City = 'NewYork'
) a
CROSS JOIN (
    SELECT COUNT(B.Id)
    FROM Table_B AS B
    WHERE B.City = 'England'
) b
CROSS JOIN (
    SELECT COUNT(C.Id)
    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.

CodePudding user response：

As all queries return a scalar value 8one row one column you can simply add or subtract them

SELECT 
    (SELECT 
            COUNT(A.Id)
        FROM
            Table_A AS A
        WHERE
            A.City = 'NewYork') - (SELECT 
            COUNT(B.Id)
        FROM
            Table_B AS B
        WHERE
            B.City = 'England') - (SELECT 
            COUNT(C.Id)
        FROM
            Table_C AS C
        WHERE
            C.City = 'Berlin') as Whatever