mysql - tiene - ¿Cómo contar el número de instancias de cada ID de clave externa en una tabla?

optimizar consultas lentas mysql

Question

Aquí está mi pregunta SQL simple ...

Tengo dos mesas:

Libros

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

Pedidos

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

Me gustaría crear una consulta que devuelva:

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

En otras palabras, devuelva cada columna para TODAS las filas en la tabla Libros, junto con una columna calculada llamada ''número_de_ordenes'' que cuenta la cantidad de veces que cada libro aparece en la tabla Pedidos. (Si un libro no aparece en la tabla de órdenes, el libro debe aparecer en el conjunto de resultados, pero "number_of_orders" debe ser cero .

Hasta ahora, he llegado a esto:

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

Eso es casi correcto, pero no del todo, porque "number_of_orders" será 1 incluso si un libro nunca aparece en la tabla Pedidos. Además, dada mi falta de conocimiento de SQL, estoy seguro de que esta consulta es muy ineficiente.

¿Cuál es la forma correcta de escribir esta consulta? (Por lo que vale, esto tiene que funcionar en MySQL, por lo que no puedo usar ninguna otra característica específica del proveedor).

¡Gracias por adelantado!

Answer 1

Cambiar count(*) para count(orders.book_id)

Answer 2

Estás contando lo incorrecto. Desea contar los book_id no nulos.

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

Answer 3

Su consulta es casi correcta y es la forma correcta de hacerlo (y la más eficiente)

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(*) podría incluir valores NULL en el recuento porque cuenta todas las filas, mientras que COUNT(orders.book_id) no lo hace porque ignora los valores NULL en el campo dado.

Answer 4

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)

Answer 5

select author.aname,count(book.author_id) as "number_of_books"
from author 
left join book 
on(author.author_id=book.author_id)
GROUP BY author.aname;