View Full Version : Is JOIN with SUM() possible?

05-13-2005, 11:05 AM
I am having difficulty coming up with a valid syntax, and I don't even know if it is possible.

I have two tables: orders and order_item

orders has information like customer_id, date_purchased and so on.

order_item has:

id (key)

What I need to do is to get all (*) fields from orders, and (SUM(order_item.quantity) WHERE orders.id = order_item.orders_id) all in a single sql command.

Basically the syntax I am currently trying to get to work is:

SELECT a.*, SUM(b.quantity) FROM orders AS a, order_item AS b WHERE a.company_id = 3 AND a.id = b.orders_id

I have a feeling that I need to do a LEFT JOIN as a nested statement... Any ideas?

05-13-2005, 11:01 PM
SELECT a.*, SUM(b.quantity) FROM orders AS a LEFT JOIN order_item AS b ON a.id = b.orders_id WHERE a.company_id = 3

05-13-2005, 11:08 PM
Thanks for the assistance but running that query I get the following error:

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

05-14-2005, 02:00 AM
That has nothing to do with the JOIN, it's because you haven't got a GROUP clause for your SUM to work with.