mysql - 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.