MySQL JOIN and COUNT in single query

Question

I'm trying to join 2 tables together and get the count of foreign keys... I'm sorry, but I don't really how to explain myself, so let me demonstrate:

I have 1 table, 'orders', for orders, with the following fields:

id, f_name, l_name, credit_card, ETC.

Then, I have an 'orders_details' table for the items in the order, like so:

id, order_id, product_id, qty

Now, I want to run a query joining the 2 tables, getting 1 row per each row in the orders table, with a column telling me how many products are in each order.

Anybody know how to achieve this?

P.S. I'd also like to be able to get the total of all the 'qty' for the orders (I don't want to run a separate query for each order).

Answer 1

SELECT o.id, o.f_name, o.l_name, COUNT(od.id), COALESCE(SUM(od.qty), 0)
FROM orders o
LEFT JOIN order_details od ON o.id = od.order_id
GROUP BY o.id, o.f_name, o.l_name