...

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



dreystone
05-13-2005, 10: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)
orders_id
product_id
quantity
unit_price
total_price

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?

hemebond
05-13-2005, 10: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

dreystone
05-13-2005, 10: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

hemebond
05-14-2005, 01: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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum