Search code examples
mysqlduplicates

Count duplicates records in Mysql table?

I have table with, folowing structure.

tbl 

id   name  
1    AAA
2    BBB
3    BBB
4    BBB
5    AAA
6    CCC

select count(name) c from tbl
group by name having c >1

The query returning this result:

AAA(2)  duplicate
BBB(3)  duplicate
CCC(1)  not duplicate

The names who are duplicates as AAA and BBB. The final result, who I want is count of this duplicate records.

Result should be like this: Total duplicate products (2)

Solution

  • The approach is to have a nested query that has one line per duplicate, and an outer query returning just the count of the results of the inner query.

    SELECT count(*) AS duplicate_count
FROM (
 SELECT name FROM tbl
 GROUP BY name HAVING COUNT(name) > 1
) AS t