Count duplicates records in Mysql table?

Question

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)

Answer 1

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 

Answer 2

Use IF statement to get your desired output:

SELECT name, COUNT(*) AS times, IF (COUNT(*)>1,"duplicated", "not duplicated") AS duplicated FROM <MY_TABLE> GROUP BY name 

Output:

AAA 2 duplicated BBB 3 duplicated CCC 1 not duplicated