Subtraction between two sql queries

Question

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.

Tell us a little more about the structure of the tables and what data you want to get. Maybe the queries can be re-written to select what you want with only one quesry. — Vincent Ramdhanie, Oct 19 '09 at 14:44
If you don't give table structures, sample data, expected output, how do you think we are suppose to help you???? — Eric, Apr 06 '18 at 21:10

score 58 · Answer 1 · answered Oct 19 '09 at 14:44

58

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)

answered Oct 19 '09 at 14:44

Joey

344,408
85
689
683

Hi, I tried this method and it gives me ERROR 1242 (21000) at line 4: Subquery returns more than 1 row. Any suggestions? – Nicky Mirfallah Feb 09 '16 at 21:54
Yes. Make sure that you only get a single row back, otherwise you cannot use it as a scalar. The error message is fairly clear in that regard. Maybe you should check what the difference between your problem and the one stated in the question is. – Joey Feb 09 '16 at 22:03
I'm solving the same question and this is my query: SELECT (SELECT COUNT(CITY) FROM STATION ) - (SELECT COUNT(CITY) FROM STATION GROUP BY (CITY)) AS Difference – Nicky Mirfallah Feb 09 '16 at 22:23
1

Do you perchance spot an additional `group by` in your query? Did you try the sub-queries on their own? Have you noticed something that might cause them to be unable to be treated as a scalar? – Joey Feb 10 '16 at 06:07

score 31 · Answer 2 · answered Oct 19 '09 at 14:46

31

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

answered Oct 19 '09 at 14:46

Gary McGill

26,400
25
118
202

1

THANKS this solves it:)...the simplest thing...ahhh sometimes – andreas Oct 19 '09 at 14:54
1

Thanks! It was so simple, yet all over the web people are suggesting doing extravagant SQL logic to get it when a simple minus suffices. – Siddhartha Apr 22 '15 at 17:53
1

also adding DISTINCT to the query, this is how you would do it `SELECT (SELECT COUNT(CITY) FROM STATION) - (SELECT COUNT(DISTINCT CITY) FROM STATION)` – tsando Apr 09 '18 at 10:09

score 16 · Answer 3 · edited Nov 26 '19 at 21:43

16

This will return the difference

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

edited Nov 26 '19 at 21:43

Dharman

30,962
25
85
135

answered Nov 26 '19 at 21:41

sourabh bodkhe

161
1
3

I believe that this answer is not correct. Perhaps I have misunderstood something. Could you elaborate a bit? – Binarus Nov 26 '19 at 21:51

score 5 · Answer 4 · answered Dec 09 '16 at 08:12

5

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

answered Dec 09 '16 at 08:12

Sven M.

59
1
5

score 3 · Answer 5 · edited Jul 19 '19 at 14:05

3

The query is like below :

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

edited Jul 19 '19 at 14:05

Amira Bedhiafi

8,088
6
24
60

answered Oct 03 '18 at 13:42

Ayesha Shaik

31
1

score 2 · Answer 6 · edited Mar 24 '20 at 09:35

2

This can be done in a single query:

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

edited Mar 24 '20 at 09:35

TT.

15,774
6
47
88

answered Mar 24 '20 at 09:01

Sanjay C

41
3

1

Hi Sanjay. True, but you're only echoing another answer here which was posted some time ago. Please do not echo existing answers. – TT. Mar 24 '20 at 09:36

score 1 · Answer 7 · answered Oct 19 '09 at 14:44

1

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

answered Oct 19 '09 at 14:44

John Lechowicz

2,573
3
21
34

score 1 · Answer 8 · answered Oct 19 '09 at 14:45

1

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

answered Oct 19 '09 at 14:45

Justin Niessner

242,243
40
408
536

score 1 · Answer 9 · answered Oct 19 '09 at 14:51

1

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

answered Oct 19 '09 at 14:51

Philip Kelley

39,426
11
57
92

score 1 · Answer 10 · edited Apr 06 '18 at 21:07

1

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;

edited Apr 06 '18 at 21:07

marc_s

732,580
175
1,330
1,459

answered Mar 28 '18 at 00:43

Csaxena

901
6
3

score 0 · Answer 11 · edited Jul 19 '19 at 14:19

0

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

edited Jul 19 '19 at 14:19

Amira Bedhiafi

8,088
6
24
60

answered Oct 22 '17 at 07:40

Faysal Ahmed Raju

11
2

Subtraction between two sql queries

11 Answers11

Linked