How To Find Duplicate Values In MySQL


Short Answer

Do a SELECT with a GROUP BY clause.

1
2
3
4
SELECT col, COUNT(*) count 
FROM TABLE 
GROUP BY col 
HAVING count > 1;

Suppose col is the column we want to find duplicate values in. This will return a result with the col value in the first column, and a count of how many times that value appears in the second column.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

-- fetch 
SELECT GROUP_CONCAT(empId) ids, dept, COUNT(*) count 
FROM EMPLOYEE 
GROUP BY dept 
HAVING count > 1;

Table EMPLOYEE:

1
2
3
4
5
6
7
+-------+-------+------------+
| empId	| name  | dept       |
+-------+-------+------------+
|     1 | Clark	| Sales      |
|     2	| Dave	| Accounting |
|     3	| Ava	| Sales      |
+-------+-------+------------+

Query output:

1
2
3
4
5
+-----+-------+-------+
| ids | dept  | count |
+-----+-------+-------+
| 1,3 | Sales | 2     |
+-----+-------+-------+




Related Posts

How To Specify Unique Constraint For Multiple Columns In MySQL

How to make multiple columns unique (together) in an existing...

How To Select One Row Per ID With Max Value On A Column

Suppose we have the following REIMBURSEMENT table, find the max...

How To Get The Size Of A Table In MySQL

How to get the size of a table in MySQL...

How To Get The Size Of A Database In MySQL

How to get the size of a database in MySQL...

How To Concatenate Multiple Rows Into One Field In MySQL

How to concatenate multiple rows into one field in MySQL...

How To Change The Data Type Of A Column In MySQL

Given a table EMPLOYEE, change the data type of the...