On 4/5/08, Jonathan Mangin <jon.mangin@stripped> 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
Pay particular attention to
- Be Precise
- and Don't Claim You Have Found A
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
Your "bug" is likely in the GROUP BY.
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.