views:

1575

answers:

5

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.

+5  A: 

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)
Joey
A: 

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

jlech
A: 
select @result = (select count(0) from table1) - (select count(0) from table2)
Justin Niessner
+1  A: 
SELECT (SELECT COUNT(*) FROM t1) - (SELECT COUNT(*) FROM t2)
Gary McGill
THANKS this solves it:)...the simplest thing...ahhh sometimes
andreas
A: 
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
Philip Kelley