CODEWITHSUNDEEP
mysql
PinoyStackOverflower
PinoyStackOverflower

Reputation: 5302

MySQL Counting number of duplicate data in a table

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!

Upvotes: 2

Views: 1119

Answers (1)

Joe Landsman
Joe Landsman

Reputation: 2177

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

Upvotes: 5

Related Questions