Finding difference between total value and distinct value in a table

Question

Input Table: City

  +-----------+
  |   Name    |
  +-----------+
  |   AAA     |
  +-----------+
  |   AAA     |
  +-----------+
  |   BBB     |
  +-----------+
  |   CCC     |
  +-----------+

The table City has one column Name. I want output that will return the difference between total numbers of city name and distinct number of city name.

In case of this table the result would be 1.

Answer 1

Use COUNT and DISTINCT COUNT.

SELECT COUNT(name)-COUNT(DISTINCT name) AS nameDifference
FROM city

Output:

nameDifference
1

Answer 2

Try this

SELECT COUNT(Name)-COUNT(DISTINCT Name) AS NewName
FROM city