Find mysql duplicate records

If you have a mysql table and want to retrieve which are duplicated for one or more rows, here is how to do this : 1) You have to group results by the column(s) itself 2) Select the column(s) and its count as well 3) Use having to only get those that their count is greater than 1

SELECT 

    col, 

    COUNT(col)

FROM

    table_name

GROUP BY col

HAVING COUNT(col) > 1;

//-------------------------------------- EX

SELECT 

    email, 

    COUNT(email)

FROM

    contacts

GROUP BY email

HAVING COUNT(email) > 1;

SELECT 

    col1, COUNT(col1),

    col2, COUNT(col2),

    ...



FROM

    table_name

GROUP BY 

    col1, 

    col2, ...

HAVING 

       (COUNT(col1) > 1) AND 

       (COUNT(col2) > 1) AND 

       ...

//-------------------------------------- EX

SELECT 

    first_name, COUNT(first_name),

    last_name,  COUNT(last_name),

    email,      COUNT(email)

FROM

    contacts

GROUP BY 

    first_name , 

    last_name , 

    email

HAVING  COUNT(first_name) > 1

    AND COUNT(last_name) > 1

    AND COUNT(email) > 1;



//------------------------------- EX2

SELECT 

	id, parlNumber, SESSION, billNumberPrefix, billNumber, title, originatingChamber, introduced_date, 

	COUNT(parlNumber) AS parlNumberCount,

	COUNT(SESSION) AS sessionCount,

	COUNT(billNumberPrefix) AS billNumberPrefixCount,

	COUNT(billNumber) AS billNumberCount

FROM

    billsfederal

GROUP BY 

    parlNumber , 

    SESSION , 

    billNumberPrefix,

    billNumber

HAVING  COUNT(parlNumber) > 1

    AND COUNT(session) > 1

    AND COUNT(billNumberPrefix) > 1

    AND COUNT(billNumber) > 1

    ;



//------------------------------------- EX3

SELECT 

	id, parlNumber, SESSION, billNumberPrefix, billNumber, title, originatingChamber, introduced_date, 

	COUNT(parlNumber) AS parlNumberCount,

	COUNT(SESSION) AS sessionCount,

	COUNT(billNumberPrefix) AS billNumberPrefixCount,

	COUNT(billNumber) AS billNumberCount,

	COUNT(title) AS titleCount

FROM

    billsfederal

GROUP BY 

    parlNumber , 

    SESSION , 

    billNumberPrefix,

    billNumber,

    title

HAVING  COUNT(parlNumber) > 1

    AND COUNT(session) > 1

    AND COUNT(billNumberPrefix) > 1

    AND COUNT(billNumber) > 1

    AND COUNT(title) > 1

    ;

Mohammed Anwar

Experienced technical lead PHP, MySQL and Laravel Developer for 15+ years, with proven ability to develop and create high-quality and optimized web applications. Great ability to build and optimize database design, schema and queries. Versed programing trainer and instructor delivering web courses and helping others to get into the field in a timely manner. Fast and eager learner for new technologies .