HackerRank SQL — Finding the Difference Between Total and Distinct City Counts

This one was actually pretty fun to think about. The problem wanted me to find the difference between the total number of city entries and the number of unique city entries. Basically how many duplicates are hiding in the table.

The idea is simple. COUNT gives you the total rows and COUNT with DISTINCT gives you only the unique ones. Subtract one from the other and you have your answer.

Here is what I wrote:

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

COUNT(CITY) counts every single row in the CITY column including duplicates. COUNT(DISTINCT CITY) counts only the unique city names. When you subtract the second from the first you get the number of city names that are repeated in the table.

The interesting thing here is that both counts are happening inside a single SELECT. You do not need two separate queries or anything complicated. SQL lets you do the math right there in the SELECT line itself.

This was the first problem where I had to think about aggregation a little. COUNT is one of those functions you will use constantly in SQL so getting comfortable with it early on is really worth it.