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 )

Tags:

Sql

Postgresql