OpenWritings.net

SELECT column1, column2, columnX, count(*) AS Duplicates FROM tablename
GROUP BY column1, column2, columnX
HAVING count(*) > 1

-- Create a table t.
CREATE TABLE t
(
  a INT,
  b VARCHAR(5),
  c VARCHAR(5)
);
 
-- Insert some values into table t.
INSERT INTO t VALUES(3, 'j', 'h' );
INSERT INTO t VALUES(3, 'j', 'h' );
INSERT INTO t VALUES(3, 'j', 'Xh' );
INSERT INTO t VALUES(6, 'u', 'g' );
INSERT INTO t VALUES(6, 'u', 'g' );
INSERT INTO t VALUES(4, 'y', 'k' );
INSERT INTO t VALUES(1, 'w', 'x' );
 
-- Show values that are being inserted.
SELECT * FROM t ORDER BY a;
 
-- Show duplicate values for column a and b.
SELECT a, b, count(*) AS Duplicates FROM t
GROUP BY a, b
HAVING count(*) > 1;

SELECT * FROM t ORDER BY a;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | w    | x    |
|    3 | j    | h    |
|    3 | j    | h    |
|    3 | j    | Xh   |
|    4 | y    | k    |
|    6 | u    | g    |
|    6 | u    | g    |
+------+------+------+
 
SELECT a, b, count(*) as Duplicates FROM t
GROUP BY a, b
HAVING count(*) > 1;
+------+------+------------+
| a    | b    | Duplicates |
+------+------+------------+
|    3 | j    |          3 |
|    6 | u    |          2 |
+------+------+------------+