On Mon, Apr 14, 2008 at 5:40 PM, Jonathan Mangin <[EMAIL PROTECTED]> 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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
