Re: Incorrect results from sum

["Jake Peavy" <djstunks@xxxxxxxxx>]

On 4/5/08, Jonathan Mangin <jon.mangin@xxxxxxxxxxx> wrote:
>
> I'm getting incorrect results from a sum and wonder if
> anyone sees something obviously wrong. (Won't surprise
> me.) Leaving 'simple' out of the equation (or adding
> 'simple' values manually) gets me the correct number.
>
> $menu is a personalized table of meal/recipe ingredients.
> itemized is a list of ingredients consumed in each meal.
> simple is a table of nutrient totals for each meal.
>
> (I want the user to be able to choose simple or
> itemized storage at any time.)
>
> $menu.carb is decimal(8,3)
> simple.carb is decimal(4,1)
>
> select round(sum($menu.carb * units) + simple.carb,2)
> from itemized inner join simple on itemized.uid = simple.uid
> inner join $menu on itemized.personal_id = $menu.id
> where itemized.uid = ? and itemized.date between ? and ?
> group by date;
>
> It's supposed to return 253.08, but I keep getting 260.36.
>

Jonathan, I think it would behoove you to read "How To Ask Questions The
Smart Way".

Pay particular attention to

   - Be Precise
   <http://catb.org/%7Eesr/faqs/smart-questions.html#beprecise>
   - and Don't Claim You Have Found A
Bug<http://catb.org/%7Eesr/faqs/smart-questions.html#id306810>

In this case, I believe you should post example table definitions, sample
data, and the exact query which replicates the issue.  Don't use your actual
tables - create the smallest, simplest sample tables which still experience
the issue.

Your "bug" is likely in the GROUP BY.

-- 
-jp


Many people never stop to realize that a tree is a living thing, not that
different from a tall, leafy dog that has roots and is very quiet.

deepthoughtsbyjackhandy.com