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

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

Answer 2

For List:

SELECT COUNT(`name`) AS adet, name
FROM  `tbl` WHERE `status`=1 GROUP BY `name`
ORDER BY `adet`  DESC

For Total Count:

    SELECT COUNT(*) AS Total
    FROM (SELECT COUNT(name) AS cou FROM tbl GROUP BY name HAVING cou>1 ) AS virtual_tbl 

// Total: 5

Answer 3

why not just wrap this in a sub-query:

SELECT Count(*) TotalDups
FROM
(
    select Name, Count(*)
    from yourTable
    group by name
    having Count(*) > 1
) x

See SQL Fiddle with Demo

Answer 4

The accepted answer counts the number of rows that have duplicates, not the amount of duplicates. If you want to count the actual number of duplicates, use this:

SELECT COALESCE(SUM(rows) - count(1), 0) as dupes FROM(

    SELECT COUNT(1) as rows
    FROM `yourtable`
    GROUP BY `name`
    HAVING rows > 1

) x

What this does is total the duplicates in the group by, but then subtracts the amount of records that have duplicates. The reason is the group by total is not all duplicates, one record of each of those groupings is the unique row.

Fiddle: http://sqlfiddle.com/#!2/29639a/3

Answer 5

SQL code is:

SELECT VERSION_ID, PROJECT_ID, VERSION_NO, COUNT(VERSION_NO) AS dup_cnt
FROM MOVEMENTS
GROUP BY VERSION_NO
HAVING (dup_cnt > 1 && PROJECT_ID = 11660)

Answer 6

I'm using this query for my own table in PHP, but it only gives me one result whereas I'd like to the amount of duplicate per username, is that possible?

SELECT count(*) AS duplicate_count
FROM (
 SELECT username FROM login_history
 GROUP BY username HAVING COUNT(time) > 1
) AS t;