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

Phil · Accepted Answer · 2011-05-04 01:52:08Z

12

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

edited May 4, 2011 at 1:52

answered May 4, 2011 at 1:40

Phil

166k25 gold badges265 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phil Over a year ago

@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 Over a year ago

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

Collectives™ on Stack Overflow

MySQL JOIN and COUNT in single query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related