Subtraction between two sql queries

问题:

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.

回答1:

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)

回答2:

SELECT (SELECT COUNT(*) FROM t1) - (SELECT COUNT(*) FROM t2)

回答3:

I know this is an old post but here is another solution that fit best to my needs (tested on firebird)

SELECT c1-c2 from (select count(*) c1 from t1), (SELECT COUNT(*) c2 from t2);

回答4:

Just create an inline function with your query logic, and have it return the result. Pass in parameters as needed.

回答5:

select @result = (select count(0) from table1) - (select count(0) from table2)

回答6:

SELECT
   t1.HowManyInTable1
  ,t2.HowManyInTable2
  ,t1.HowManyInTable1 = t2.HowManyInTable2  Table1_minus_Table2
 from (select count(*) HowManyInTable1 from Table1) t1
  cross join (select count(*) HowManyInTable2 from Table2) t2

回答7:

SELECT (count(*) from t1) - (count(*) from t2);

this worked for me.

Also if there is only one table you can also do:

SELECT (count(column1)) - count(column2)) from table; 

回答8:

SELECT (select COUNT(FIRSTNAME) FROM TRMDW.EMPLOYEE1) - (SELECT COUNT(DISTINCT FIRSTNAME) FROM TRMDW.EMPLOYEE1) as difference from dual;

回答9:

((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(m,'/',2),'/',-1) FROM ms WHERE ms.id=t.m_id)-(SELECT COUNT(id) FROM t AS tr WHERE tr.m_id=t.m_id) ) AS remaining