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:
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 * 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)
Comments
Post a Comment