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

Solution

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

Comments

[Phil]

@John Just remember, if you add more columns to the SELECT clause, you'll also need to add them to the GROUP BY

[Andriy M]

@John: Actually in MySQL you don't have to include all the non-aggregated columns/expression in GROUP BY. MySQL allows you to use an abridged GROUP BY where you just need to include the key columns or expressions which uniquely define all other non-aggregated data you are selecting. Still I think Phil's advice is worth following, since most other RDBMSes do not support abridged GROUP BYs, and who knows if you will not decide to start learning and using another SQL dialect.