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.

Sondra

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. – Wreath

If you don't give table structures, sample data, expected output, how do you think we are suppose to help you???? – Broccoli

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

Specht

Hi, I tried this method and it gives me ERROR 1242 (21000) at line 4: Subquery returns more than 1 row. Any suggestions? – Stocky

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. – Specht

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 – Stocky

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? – Specht

Answer 2

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

Purely

THANKS this solves it:)...the simplest thing...ahhh sometimes – Sondra

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. – Beaman

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) – Hollingshead

@Hollingshead what a spoiler of the HackerRank challenge ;) – Umberto

Answer 3

This will return the difference

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

Interbreed

I believe that this answer is not correct. Perhaps I have misunderstood something. Could you elaborate a bit? – Petigny

Answer 4

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

Ilyse

Answer 5

The query is like below :

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

Arsphenamine

Answer 6

This can be done in a single query:

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

Moskow

Hi Sanjay. True, but you're only echoing another answer here which was posted some time ago. Please do not echo existing answers. – Artefact

Answer 7

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

Inenarrable

Answer 8

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

Baeyer

Answer 9

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

Vociferate

Answer 10

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; 

Sail

Answer 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

Signalize