SQL to calculate author with most books
<p>I have a table of books, a table of authors, and a &quot;linker&quot; table (many to many links between authors/books).</p>
<p>How do I find the authors with the highest number of books?</p>
<p>This is my schema:</p>
<blockquote>
<p><strong>books</strong> : rowid, name</p>
<p><strong>authors</strong> : rowid, name</p>
<p><strong>book_authors</strong> : rowid, book_id, author_id</p>
</blockquote>
<p>This is what I came up with: (but it doesn't work)</p>
<pre><code>SELECT count(*) IN book_authors 
WHERE (SELECT count(*) IN book_authors 
       WHERE author_id = author_id)
</code></pre>
<p>And ideally I would like a report of the top 100 authors, something like:</p>
<pre><code> author_name      book_count
 -----------------------------------
 Johnny           25
 Kelly            12
 Ramboz           10
</code></pre>
<p>Do I need some kind of join? What is the fastest approach?</p>