How do I find duplicate entries in a database table?
A nested query can do the job.
SELECT author_last_name, dewey_number, NumOccurrences
FROM author INNER JOIN
( SELECT author_id, dewey_number, COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY author_id, dewey_number
HAVING ( COUNT(dewey_number) > 1 ) ) AS duplicates
ON author.id = duplicates.author_id
(I don't know if this is the fastest way to achieve what you want.)
Update: Here is my data
SELECT * FROM author;
id | author_last_name
----+------------------
1 | Fowler
2 | Knuth
3 | Lang
SELECT * FROM book;
id | author_id | dewey_number | title
----+-----------+--------------+------------------------
1 | 1 | 600 | Refactoring
2 | 1 | 600 | Refactoring
3 | 1 | 600 | Analysis Patterns
4 | 2 | 600 | TAOCP vol. 1
5 | 2 | 600 | TAOCP vol. 1
6 | 2 | 600 | TAOCP vol. 2
7 | 3 | 500 | Algebra
8 | 3 | 500 | Undergraduate Analysis
9 | 1 | 600 | Refactoring
10 | 2 | 500 | Concrete Mathematics
11 | 2 | 500 | Concrete Mathematics
12 | 2 | 500 | Concrete Mathematics
And here is the result of the above query:
author_last_name | dewey_number | numoccurrences
------------------+--------------+----------------
Fowler | 600 | 4
Knuth | 600 | 3
Knuth | 500 | 3
Lang | 500 | 2
You probably want this
SELECT dewey_number, author_last_name,
COUNT(dewey_number) AS NumOccurrences
FROM book
GROUP BY dewey_number,author_last_name
HAVING ( COUNT(dewey_number) > 1 )