CODEWITHSUNDEEP
mysql
cs95
cs95

Reputation: 402813

Mysql - How can I subtract the number of rows returned by 2 queries

in trying to learn MySQL, I've been solving the problems from Hackerrank. I've got stuck at this problem, which asks:

"[...]find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table."

The idea is to output a single answer N' - N.

The schema looks like this:

enter image description here

To this effect, the query I've tried is:

select count(s2.c2) - count(s1.c1) 
from (select city as c1 from station group by city) as s1, 
(select city as c2 from station) as s2;

I've also tried many other variants, but none of them give me the right answer. Where am I going wrong? I've also looked up other questions on SO but they do not return a single number.

Thank you.

Upvotes: 0

Views: 1020

Answers (2)

Norbert
Norbert

Reputation: 6084

There are different ways of doing this. To use your direction:

select total_cities - count(distinct_cities) 
from (select count(*) as distinct_cities from station group by city) as s1, 
(select count(*) as total_cities from station) as s2
GROUP BY total_cities;

So first counting the distinct_cities to have just a unique list, you could also use:

SELECT DISTINCT city AS distinct_cities FROM station

And then counting the total number of distinct cities by counting these again in the outer query. Hope this helps you to think of more (shorter) ways of writing this.

Upvotes: 1

Blank
Blank

Reputation: 12378

Just use count(distinct), and no need to use self-join, try following:

select
    count(city) - count(distinct city)
from station

Upvotes: 2

Related Questions