CODEWITHSUNDEEP
sqlsql-servert-sqlsubtraction
andreas
andreas

Reputation: 361

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.

Upvotes: 36

Views: 143382

Answers (11)

Sanjay C
Sanjay C

Reputation: 41

This can be done in a single query:

SELECT COUNT(col_name) - COUNT(DISTINCT col_name) as Difference from table_name;

Upvotes: 2

sourabh bodkhe
sourabh bodkhe

Reputation: 161

This will return the difference

SELECT COUNT(Attribute) - COUNT(DISTINCT Attribute) FROM table_name;

Upvotes: 16

Faysal Ahmed Raju
Faysal Ahmed Raju

Reputation: 11

The query is like below : 

((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

Upvotes: 0

Ayesha Shaik
Ayesha Shaik

Reputation: 31

The query is like below : 

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

Upvotes: 3

Csaxena
Csaxena

Reputation: 971

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;

Upvotes: 1

Sven M.
Sven M.

Reputation: 59

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);

Upvotes: 5

Philip Kelley
Philip Kelley

Reputation: 40309

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

Upvotes: 1

Gary McGill
Gary McGill

Reputation: 27516

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

Upvotes: 30

Justin Niessner
Justin Niessner

Reputation: 245399

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

Upvotes: 1

John Lechowicz
John Lechowicz

Reputation: 2583

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

Upvotes: 1

Joey
Joey

Reputation: 354416

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)

Upvotes: 59

Related Questions