From: Perrin Harkins (perrinelem.com)
Date: Mon Apr 14 2008 - 21:52:00 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On Mon, Apr 14, 2008 at 5:40 PM, Jonathan Mangin <jon.mangincomcast.net> wrote:
> select
> round(sum(my_menu.carb * units) + sum(simple.carb),2)
> from itemized inner join simple using (uid)
> left join my_menu on itemized.personal_id = my_menu.id;
>
> Instead of 218.3 this returns 602, which is
> (52.9 * 2 items in simple) + (165.4 * 3 items in itemized).
>
> Is it possible to get correct totals some other way with
> this table structure? Or explain why this is wrong?

Change the column clause to SELECT * and you'll see what's wrong:
you're operating on a set of six rows after doing the joins.

You can certainly get the results you want from those tables, but not
from a single query unless you use subqueries. (Well, you can use the
DISTINCT keyword with SUM, but that has the potential to wreak havoc
if you have legitimate duplicate values.)

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]