MySQL JOIN and COUNT in one query

Question

I am trying to combine two tables and get the number of foreign keys ... Sorry, but I really can’t 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 a table "orders_details" for the items in order, for example:

 id, order_id, product_id, qty 

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

Does anyone know how to achieve this?

PS I would also like to get the total number of all "qty" for orders (I do not want to run a separate request 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