CODEWITHSUNDEEP
sqlsqlite
xralf
xralf

Reputation: 3712

Select count from particular foreign key in SQLite

I'd like to create an SQL statement that will tell me how many books where written by a given author.

When you know the authorid (foreign key to books table) it's easy:

select count(*) from books where authorid = 25;
15

You can see that author with id 15 has written 15 books. Is it possible to create a statement for all the authors such that the ouput is as follows?

author_id, author_name, number_of_books
1          Michael      15
2          Robin        7
...

Upvotes: 0

Views: 509

Answers (3)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

You could do this with a group by clause:

select 
    a.author_id,
    a.author_name,
    count(*) as number_of_books
from
    authors a inner join
    books b on b.author_id = a.author_id
group by
    a.author_id,
    a.author_name
order by 
    number_of_books

Upvotes: 2

dotancohen
dotancohen

Reputation: 31511

SELECT
    books.author_id, authors.author_name, COUNT(books.author_id) AS number_of_books
FROM
    authors INNER JOIN books ON books.author_id = authors.id
GROUP BY
    author_name;

Also, make sure that the books.author_id column is NOT NULL otherwise performance will suffer.

Upvotes: 1

stuartd
stuartd

Reputation: 73293

This would be a GROUP BY query: 

select author_id, author_name, count(*) as number_of_books 
   from books
   join author on books.author_id = author.id 
   group by author_id, author_name;

Upvotes: 2

Related Questions