find duplicates using sql
This question has been asked many times, how to find duplicate values in a database table. Many times if you are loading data from another database or table you might load the data more than once or you were controlling the data integrity at front end which didn't worked as expected and now you have duplicate values in some columns. As an example you might have a table called Customers and a field called Username, this column is suppose to be unique but now you have some duplicate usernames. If you want to count how many usernames are duplicated and how many usernames are still unique then you can use the GROUP BY and HAVING clause to find duplicate values.
SELECT username, COUNT(*) FROM customers_table
GROUP BY username HAVING COUNT(*) > 1
Now you can use the above technique to find duplicate rows in more than one column. If you want to find duplicates only where username and email address are same then we can add the email column in it.
SELECT username, email, COUNT(*) FROM customers_table
GROUP BY username, email HAVING COUNT(*) > 1
The same logic can be changed to find non duplicate rows only by changing the HAVING COUNT(*) > 1 to = 1, this will give us nonduplicated usernames.
SELECT username, COUNT(*) FROM customers_table
GROUP BY username HAVING COUNT(*) = 1