Re: Incorrect results from sum

["Perrin Harkins" <perrin@xxxxxxxx>]

On Mon, Apr 14, 2008 at 5:40 PM, Jonathan Mangin <jon.mangin@xxxxxxxxxxx> 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/mysql?unsub=royale@xxxxxxxxxx