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 300I 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 apprec开发者_StackOverflow中文版iated. Thanks!
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
精彩评论