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 = 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 <jnichel@stripped> wrote:
> 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
> john@stripped
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
--
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/
