mysql find duplicates in same table

1SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

1SELECT col, COUNT(col) FROM table_name GROUP BY col HAVING COUNT(col) > 1;

1SELECT 
2    col1, COUNT(col1),
3    col2, COUNT(col2)
4FROM
5    table_name
6GROUP BY 
7    col1, 
8    col2
9HAVING 
10       (COUNT(col1) > 1) AND 
11       (COUNT(col2) > 1);
12

1## Find ALL duplicate recods by value (without grouping them by value) ##
2# to find the duplicate, 
3# replace all instances of tableName with your table name
4# and all instances of duplicateField with the field name where you look for duplicates
5SELECT t1.*
6FROM tableName AS t1
7INNER JOIN(
8	SELECT duplicateField
9	FROM tableName
10	GROUP BY duplicateField
11	HAVING COUNT(duplicateField) > 1
12)temp ON t1.duplicateField = temp.duplicateField
13order by duplicateField

1
2            
3                
4            
5         SELECT 
6    col, 
7    COUNT(col)
8FROM
9    table_name
10GROUP BY col
11HAVING COUNT(col) > 1;
12Code language: SQL (Structured Query Language) (sql)

1SELECT firstname, 
2   lastname, 
3   list.address 
4FROM list
5   INNER JOIN (SELECT address
6               FROM   list
7               GROUP  BY address
8               HAVING COUNT(id) > 1) dup
9           ON list.address = dup.address;
10