How to find repeated rows and display there count in a separate column with MySQL?

For this, use the GROUP BY HAVING clause. Let us first create a table −

mysql> create table DemoTable(    Name varchar(100),    Age int ); Query OK, 0 rows affected (1.50 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('Chris',23); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('David',21); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris',23); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Chris',21); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Mike',25); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('David',21); Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------+------+ | Name  | Age  | +-------+------+ | Chris | 23   | | David | 21   | | Chris | 23   | | Chris | 21   | | Mike  | 25 | | David | 21   | +-------+------+ 6 rows in set (0.00 sec)

Following is the query to find repeated rows and display count in a separate column −

mysql> select Name,Age,count(*) AS Repeated from DemoTable group by Name,Age having Repeated > 1;

This will produce the following output −

+-------+------+----------+ | Name  | Age  | Repeated | +-------+------+----------+ | Chris | 23   | 2        | | David | 21   | 2       | +-------+------+----------+ 2 rows in set (0.00 sec)