How do we count the total duplicate records in a column of MySQL table?

Suppose we have the following table named stock_item in which the column quantity is having duplicate values i.e. for item name ‘Notebooks’ and ‘Pencil’, the column ‘Quantity’ is having duplicate values ‘40’ and for items ‘Shirts’, ‘Shoes’ and ‘Trousers’ triplicate value 29 is hold by column ‘quantity’ as shown in the table.

mysql> Select * from stock_item;
+------------+----------+
| item_name  |quantity  |
+------------+----------+
| Calculator |       89 |
| Notebooks  |       40 |
| Pencil     |       40 |
| Pens       |       32 |
| Shirts     |       29 |
| Shoes      |       29 |
| Trousers   |       29 |
+------------+----------+
7 rows in set (0.00 sec)

It shows that there are two duplicate values i.e. 40 and 29 in ‘quantity’.

Now with the help of the following query, we can count the total duplicate records in the column ‘quantity’ of table ‘stock_item’.

mysql> Select count(*) AS Total_duplicate_count
    -> FROM (SELECT item_name FROM stock_item
    -> GROUP BY quantity HAVING COUNT(quantity) > 1) AS X;
+-----------------------+
| Total_duplicate_count |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

Abhinaya

e

Updated on: 13-Feb-2020

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started