query to get the difference between total number of rows and distinct rows

Question

I'm new to SQL, wasn't able to write the correct SQL. Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+
| Field       |   Type     |
+-------------+------------+
| ID          | INTEGER    |
| CITY        | VARCHAR(21)|
| STATE       | VARCHAR(2) |
| LAT_N       | NUMERIC    |
| LONG_W      | NUMERIC    |
+-------------+------------+

Let NUM be the number of cities and NUMunique be the number of unique cities, then write a query to print the value of NUM - NUMunique.

I tried:

select (count(CITY)- distinct count(CITY)) from STATION; 

Answer 1

You can use the select distinct inside the count and try this way

select  (count(CITY)- count(distinct CITY)) from STATION;