How to Know The Duplicate Records Based on The Same Value in a Field in MySQL Database

Have you ever wanted to know which record has duplicate content based on the same content in a field in MySQL database? This information will be get after you select the records and display them which one have the same content by groupping them by that field. Well, here is the script how you can do that.

Suppose we have a table named mytable which has these following records inside:

+----+-------+
| id | name  |
+----+-------+
|  1 | One   |
|  2 | One   |
|  3 | Two   |
|  4 | Two   |
|  5 | Two   |
|  6 | Three |
|  7 | Three |
|  8 | Three |
|  9 | Three |
| 10 | Four  |
| 11 | Five  |
+----+-------+
11 rows in set (0.00 sec)

and you want to display which one has the duplicate content in the name filed become like this:

+-------+---------+
| name  | howmany |
+-------+---------+
| Three |       4 |
| Two   |       3 |
| One   |       2 |
+-------+---------+
3 rows in set (0.00 sec)

In the other words, we will display the value in name field which have more than one records, whereas, the records which do not the duplicate content, will be avoided (in this case the record which have the value in field name-nya: Four and Five).

Well, here is the SQL script to do make it came true:

SELECT name, COUNT(name) AS howmany 
FROM `my_table`
GROUP BY name 
HAVING howmany > 1
ORDER BY howmany DESC;