MySQL Counting number of duplicate data in a table

Question

How do I get the number of dumplicate data in a table.

Example: I have these datas in a table.

ID: 1 2 3 4 5
Score: 100 300 400 100 300

I want the result to be 2 because 100 has a duplicate andalso 300, so that gives the result to be 2.

I'm thinking of a group by and count aggregate function. But it won't work because it will include 400 even if it doesn't have duplicate data.

How would I do t that?

Your help would be greatly appreciated. Thanks!

Joe Landsman · Accepted Answer · 2011-08-21 03:06:15Z

5

You need to use a GROUP BY as well as a HAVING clause. Like this:

SELECT COUNT(*)
FROM table
GROUP BY Score
HAVING COUNT(Score) > 1

answered Aug 21, 2011 at 3:06

Joe Landsman

2,17713 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1