0

I have table with, following structure. 

id   name  
1    john
2    ana
3    john
4    ana
5    peter
6    ana
7    Abrar
8    Raju

Duplicate entries in the table are as follows

john(2)  duplicate
ana(3)  duplicate

The names which are duplicates are john and ana.

My question is how would I count the records in total which are duplicate here it is '5' records

Note : I also followed the similar question in community but it explains how we can add the number of duplicates exists for that particular name in the table and adds up the third column in table representing the duplicates records with same name but in my case I wanted to know the number of all duplicates exist in the table (here the result of the query is just number "5") irrespective of the names.

Mohammed Abrar Ahmed
  • 2,290
  • 3
  • 15
  • 33
  • https://stackoverflow.com/questions/12528644/count-duplicates-records-in-mysql-table and https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql – Abdulla Nilam Jan 29 '18 at 06:33
  • @DeadManAlive Good links, but the queries in all 3 accepted answers are not identical to this question. – Tim Biegeleisen Jan 29 '18 at 06:54

1 Answers1

2

Just take a count subquery on the query you already have in mind (or perhaps have already written):

SELECT SUM(cnt) AS total_duplicates
FROM
(
    SELECT COUNT(*) AS cnt
    FROM yourTable
    GROUP BY name
    HAVING COUNT(*) > 1
) t;

enter image description here

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360