MySQL Select Count Distinct – Counting and Identifying Duplicate Records

Looking for a [relatively] quick way to identify duplicate records in a table and list how many records there are for each duplicate? Look no further.

In my case, I’ve got a table named Searches which tracks queries on one of my sites. I wanted to check the top queries, so I used this SQL statement:

1
2
3
4
5

SELECT COUNT(*) AS repetitions, Query
FROM Searches
GROUP BY Query
HAVING repetitions > 1
ORDER BY repetitions DESC;

There you have it. A simple, effective way to count and label duplicate records.

Bookmark this page