CODEWITHSUNDEEP
mysql
Zeus
Zeus

Reputation: 2253

NOT IN SQL query not working

This is a question from Hacker Rank.

Let NUM be the number of CITY entries in STATION table, and NUMunique be the number of unique cities. Query the value of NUM−NUMunique from STATION.

This is the query that I've written.

SELECT COUNT(CITY) FROM STATION WHERE CITY NOT IN (SELECT COUNT(DISTINCT CITY) FROM STATION);

This is yielding the wrong answer. Any idea what am I doing wrong here.

Upvotes: 0

Views: 51

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94642

You can use the DISTINCT keyword inside a COUNT() to get your answer in one simple query

SELECT COUNT(CITY) - COUNT(DISTINCT CITY) as the_answer FROM STATION;

Upvotes: 2

Related Questions