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.

Weather Observation Station 4 | HackerRankHackerRank

The STATION table is described as follows:

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.