This is default featured post 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.This theme is Bloggerized by Lasantha Bandara - Premiumbloggertemplates.com.

This is default featured post 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.This theme is Bloggerized by Lasantha Bandara - Premiumbloggertemplates.com.

This is default featured post 3 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.This theme is Bloggerized by Lasantha Bandara - Premiumbloggertemplates.com.

This is default featured post 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.This theme is Bloggerized by Lasantha Bandara - Premiumbloggertemplates.com.

This is default featured post 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.This theme is Bloggerized by Lasantha Bandara - Premiumbloggertemplates.com.

Monday, July 6, 2009

how to count the duplicate rows in a table

This question has been asked many times, how to find duplicate values in a database table. Many times if you are loading data table , 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_tableGROUP 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_tableGROUP BY username, email HAVING COUNT(*) > 1
SELECT username, COUNT(*) FROM customers_tableGROUP BY username 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.