Subtraction between two sql queries

andreas picture andreas · Oct 19, 2009 · Viewed 90.3k times · Source

I have 2 queries in MS SQL that return a number of results using the COUNT function.

I can run the the first query and get the first result and then run the other one to get the other result, subtract them and find the results; however is there a way to combine all 3 functions and get 1 overall result

As in: run sql1 run sql2 run SQL3 (sql1-sql2)?....

I tried them with xxxx as a function but no luck.

Answer

Joey picture Joey · Oct 19, 2009

You should be able to use subqueries for that:

SELECT
    (SELECT COUNT(*) FROM ... WHERE ...)
  - (SELECT COUNT(*) FROM ... WHERE ...) AS Difference

Just tested it:

Difference
-----------
45

(1 row(s) affected)