Как подсчитать количество экземпляров каждого идентификатора внешнего ключа в таблице?

Вот мой простой вопрос SQL...

У меня есть две таблицы:

Книги

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

Заказы

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

Я хотел бы создать запрос, который возвращает:

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

Другими словами, верните каждый столбец для строк ВСЕ в таблице "Книги" вместе с вычисленным столбцом с именем "number_of_orders" , который подсчитывает количество раз, когда каждая книга отображается в таблице "Заказы". (Если книга не встречается в таблице заказов, в результирующем наборе должна быть указана книга должна, но "number_of_orders" должна быть ноль.

До сих пор я придумал следующее:

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

Это почти правильно, но не совсем так, потому что "number_of_orders" будет равно 1, даже если книга никогда не указана в таблице Orders. Более того, учитывая мой незнание SQL, я уверен, что этот запрос очень неэффективен.

Какой правильный способ написать этот запрос? (Для чего это стоит, это должно работать на MySQL, поэтому я не могу использовать другие функции, специфичные для вендора).

Спасибо заранее!

Ответ 1

Ваш запрос почти прав, и это правильный способ сделать это (и самый эффективный)

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(*) может включать значения NULL в счетчике, потому что он подсчитывает все строки, а COUNT(orders.book_id) не потому, что он игнорирует значения NULL в заданном поле.

Ответ 2

Измените count(*) на count(orders.book_id)

Ответ 3

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)

Ответ 4

Вы считаете, что не так. Вы хотите подсчитать ненулевые book_id.

SELECT
    books.book_id,
    books.author,
    books.genre,
    books.price,
    books.publication_date,
    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,
    books.author,
    books.genre,
    books.price,
    books.publication_date