# Weather Observation Station 4

Find the difference between the total number of **CITY** entries in the table and the number of distinct **CITY** entries in the table.

{% embed url="<https://www.hackerrank.com/challenges/weather-observation-station-4/problem?isFullScreen=true>" %}

The **STATION** table is described as follows:

![](https://s3.amazonaws.com/hr-challenge-images/9336/1449345840-5f0a551030-Station.jpg)

where **LAT\_N** is the northern latitude and **LONG\_W** is the western longitude.

For example, if there are three records in the table with **CITY** values 'New York', 'New York', 'Bengalaru', there are 2 different city names: 'New York' and 'Bengalaru'. The query returns , because .

Sol:

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

Explanation:

* SELECT COUNT(CITY) - COUNT(DISTINCT CITY): This part of the query calculates the difference between two counts:
  * COUNT(CITY): This counts the total number of entries in the CITY column.
  * COUNT(DISTINCT CITY): This counts the number of distinct (unique) entries in the CITY column. By subtracting the count of distinct city entries from the total count of city entries, we get the difference.
  * FROM STATION: This specifies the table from which we want to retrieve the data, which is the STATION table.

\\
