Check for Uniqueness of values in a Column

If you want to check if a column of the table in a dataset have duplicate values, or say more than one records of the same element, then following syntax can be helpful.

1 SELECT column_name, SUM(1) AS count

2 FROM table_name

3 GROUP BY column_name

4 HAVING COUNT > 1;

This query returns the column elements that have duplicate records and also the sum of total repetitions. It is very efficient to check a uniqueness of a column in a table.

Besides uniqueness check, it can also be used in other ways. For example, we have a table "countrylanguage" listing all the countries and languages spoken in them. We are interested in seeing how many countries have made English an official Language. This query can be helpful in this case.

Snippet of table "countrylanguage"

This table contains various language spoken in different countries. We want to see how many countries have English Language as an Official Language, i.e. IsOfficial = T and Language = English.

1 SELECT language, isofficial, SUM(1) AS Count

2 FROM countrylanguage

3 WHERE isOfficial = "T" AND language = "English"

4 GROUP BY language

5 HAVING COUNT > 1;

OUTPUT

So, there are 44 countries where language = "English" have isOfficial = "T".

Reference:

Database source: https://dev.mysql.com/doc/index-other.html