How to count the number of instances of each foreign-key ID in a table?

Question

Here's my simple SQL question...

I have two tables:

Books

------------------------------------------------------- | book_id | author | genre | price | publication_date | ------------------------------------------------------- 

Orders

------------------------------------ | order_id | customer_id | book_id | ------------------------------------ 

I'd like to create a query that returns:

-------------------------------------------------------------------------- | book_id | author | genre | price | publication_date | number_of_orders | -------------------------------------------------------------------------- 

In other words, return every column for ALL rows in the Books table, along with a calculated column named 'number_of_orders' that counts the number of times each book appears in the Orders table. (If a book does not occur in the orders table, the book should be listed in the result set, but "number_of_orders" should be zero.

So far, I've come up with this:

SELECT     books.book_id,     books.author,     books.genre,     books.price,     books.publication_date,     count(*) as number_of_orders from books left join orders on (books.book_id = orders.book_id) group by     books.book_id,     books.author,     books.genre,     books.price,     books.publication_date 

That's almost right, but not quite, because "number_of_orders" will be 1 even if a book is never listed in the Orders table. Moreover, given my lack of knowledge of SQL, I'm sure this query is very inefficient.

What's the right way to write this query? (For what it's worth, this needs to work on MySQL, so I can't use any other vendor-specific features).

Thanks in advance!

Answer 1

Your query is almost right and it's the right way to do that (and the most efficient)

SELECT books.*, count(orders.book_id) as number_of_orders         from books left join orders on (books.book_id = orders.book_id) group by     books.book_id 

COUNT(*) could include NULL values in the count because it counts all the rows, while COUNT(orders.book_id) does not because it ignores NULL values in the given field.

Answer 2

SELECT b.book_id,     b.author,     b.genre,     b.price,     b.publication_date,     coalesce(oc.Count, 0) as number_of_orders from books b left join (     select book_id, count(*) as Count      from Order      group by book_id ) oc on (b.book_id = oc.book_id)