SQL Query to Find Duplicates

You can find how many times a particular field is duplicated in whole table. Assume there exist a table  named students and we want to find how many times a name duplicates. There are following total number of records:

mysql> select * from students;
+----+-----------+---------------------+------------+
| id | name      | email               | phone      |
+----+-----------+---------------------+------------+
|  1 | Alice     | alice@gmail.com     | 5487845784 |
|  2 | Bob Chris | bob@gmail.com       | 879874578  |
|  3 | Oracle    | oracle@gmail.com    | 548798453  |
|  4 | Alice     | alice2@example.com  | 85698566   |
|  5 | Alice     | alice3@example.com  | 987654789  |
|  6 | Oracle    | oracle2@example.com | 98746354   |
+----+-----------+---------------------+------------+
6 rows in set (0.00 sec)

You can see, Alice and Oracle duplicates in name field, 3 and 2 times, respectively.

You can use following SQL Query to fetch how many time any name duplicates:

select name, count(*) as duplicate_records_count from students group by name having count(*) > 1;

Here is how the output looks like when executed on MySQL command line:

mysql> select name, count(*) as duplicate_records_count from students group by name having count(*) > 1;
+--------+-------------------------+
| name   | duplicate_records_count |
+--------+-------------------------+
| Alice  |                       3 |
| Oracle |                       2 |
+--------+-------------------------+
2 rows in set (0.00 sec)