How to check for duplicates in mysql table over multiple columns

Question

I have a table of baseball players(all 1000 or so), with fields:

mysql> describe person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(30) | NO   |     | NULL    |                |
| lastname  | varchar(30) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

But I think there are some players that have gotten added in twice. How can I go through and check for how many occurrences of a particular firstname, lastname combo?

Answer 1

This provides the list of duplicates:

SELECT firstname, lastname, COUNT(*) 
FROM person 
GROUP BY firstname, lastname 
HAVING COUNT(*) > 1;

If you want to see the counts for every row remove the having clause:

SELECT firstname, lastname, COUNT(*) 
FROM person 
GROUP BY firstname, lastname;

Answer 2

SELECT firstname, lastname, count(id) count
  FROM person
 WHERE firstname = ?
   AND lasttname = ?
 GROUP BY firstname, lastname

Answer 3

For a list sorted by decreasing value of the number of copies:

SELECT firstname, lastname, COUNT(*) AS n
  FROM person
 GROUP BY firstname, lastname
 ORDER BY n DESC
 HAVING n > 1

The HAVING clause is the key part - it's necessary to filter the results after the GROUP BY clause, since a WHERE clause filters out rows before they're grouped.

Answer 4

To get id's of duplicate names as well as names do:

SELECT p1.id, p1.firstname, p1,lastname FROM person p1
INNER JOIN person p2 ON (p1.firstname = p2.firstname 
                         AND p1.lastname = p1.lastname 
                         AND p1.id <> p2.id);