Hi,
The function is probably behaving as intended, but its confusing the
hell out of me. ;) Anyway, say I have two tables; orders and lineitems
Orders has two columns: orderid(primary key) and ordertotal
Lineitems has two columns: orderid and itemid
For every orderid in the orders table, there can be one or more matching
rows in the lineitems table.
I'm trying to get the sum of all the orders, as well as count the total
number of line items with a query like this:
SELECT
Sum(a.ordertotal) as total,
Count(b.itemid) as line_items
FROM
Orders a
LEFT JOIN
Lineitems b
ON
a.orderid = b.orderid
What seems to be happening is that MySQL is adding ordertotal multiple
times for orders which have multiple line items. Eg, Say there are two
orders, both with an order total of $10. I'm expecting MySQL to return
$20 for total, and it does when each order only has one line item a
piece. However, if the first order has one line item and the second
order has two line items, MySQL returns $30 as the total. Is there a
way to make MySQL add the ordertotal column only once per unique order
in the orders table? TIA
--
John C. Nichel IV
System Administrator
KegWorks
http://www.kegworks.com
716.362.9212 x16
j...@kegworks.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
