Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Perrin Harkins (perrinelem.com)
Date: Mon Apr 14 2008 - 21:52:00 CDT
On Mon, Apr 14, 2008 at 5:40 PM, Jonathan Mangin <jon.mangincomcast.net> wrote:
> 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.)
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql