SUM() acting funny when joining

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 =3D 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
john@kegworks.com=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: SUM() acting funny when joining

John,

What's happening is that the tables do not have a one-to-one
relationship, so the JOIN duplicates rows from Orders to match the
rows in Lineitems. You need to ensure the aggregation is consistent
across the two datasets. Try this:

SELECT
Sum(a.ordertotal) as total,
line_items
FROM
Orders a
LEFT JOIN (
SELECT orderid, COUNT(*) AS line_items
FROM Lineitems GROUP BY orderid
) AS b ON
a.orderid =3D b.orderid

This may not be very efficient because the subquery in the FROM clause
will result in a temporary table without indexes.

- Baron

On Thu, Jan 14, 2010 at 5:09 PM, John Nichel wrote:
> Hi,
>
> =A0The function is probably behaving as intended, but its confusing the
> hell out of me. =A0;) =A0Anyway, say I have two tables; orders and lineit=
ems
>
> 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
> =A0 =A0 =A0 =A0Sum(a.ordertotal) as total,
> =A0 =A0 =A0 =A0Count(b.itemid) as line_items
> FROM
> =A0 =A0 =A0 =A0Orders a
> LEFT JOIN
> =A0 =A0 =A0 =A0Lineitems b
> ON
> =A0 =A0 =A0 =A0a.orderid =3D b.orderid
>
> What seems to be happening is that MySQL is adding ordertotal multiple
> times for orders which have multiple line items. =A0Eg, Say there are two
> orders, both with an order total of $10. =A0I'm expecting MySQL to return
> $20 for total, and it does when each order only has one line item a
> piece. =A0However, if the first order has one line item and the second
> order has two line items, MySQL returns $30 as the total. =A0Is there a
> way to make MySQL add the ordertotal column only once per unique order
> in the orders table? =A0TIA
>
> --
> John C. Nichel IV
> System Administrator
> KegWorks
> http://www.kegworks.com
> 716.362.9212 x16
> john@kegworks.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dbaron@xaprb.c=
om
>
>



--=20
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg