Harsh Gupta Harsh Gupta - 4 months ago 26
SQL Question

SQL to calculate author with most books

I have a table of books, a table of authors, and a "linker" table (many to many links between authors/books).

How do I find the authors with the highest number of books?

This is my schema:


books : rowid, name

authors : rowid, name

book_authors : rowid, book_id, author_id


This is what I came up with: (but it doesn't work)

SELECT count(*) IN book_authors
WHERE (SELECT count(*) IN book_authors
WHERE author_id = author_id)


And ideally I would like a report of the top 100 authors, something like:

author_name book_count
-----------------------------------
Johnny 25
Kelly 12
Ramboz 10


Do I need some kind of join? What is the fastest approach?

Answer

I'd join the three tables (via the book_authors table), group by the author, count occurrences and limit it to the top 100 rows:

SELECT   a.name, COUNT(*)
FROM     authors a
JOIN     books_authors ba ON a.rowid = ba.author_id
JOIN     books b ON ba.book_id = b.rowid
GROUP BY a.name
ORDER BY 2 DESC
LIMIT    100

EDIT:
Actually, we aren't using any data from books, just the fact the book actually exists, which can be inferred from books_authors, so this query can be improved by dropping the second join:

SELECT   a.name, COUNT(*)
FROM     authors a
JOIN     books_authors ba ON a.rowid = ba.author_id
GROUP BY a.name
ORDER BY 2 DESC
LIMIT    100
Comments