MySQL JOIN and COUNT in single query

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

---

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