List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:April 7 2008 1:48am
Subject:Re: Incorrect results from sum
View as plain text  
Hi,

On Sun, Apr 6, 2008 at 12:54 AM, Perrin Harkins <perrin@stripped> wrote:
> On Sat, Apr 5, 2008 at 9:28 PM, Jonathan Mangin <jon.mangin@stripped>
> wrote:
>  >  select itemized.day_date as day_date,
>  >  round(sum(my_menu.carb * units) + simple.carb,2)
>  >  from itemized inner join simple using (uid) inner join my_menu on
>  > itemized.personal_id = my_menu.id where itemized.uid = 'me' and
>  >  itemized.date between '2008-03-28' and '2008-04-01' group by
>  >  day_date;
>
>  You are not using a grouping operator on simple.carb or naming it in
>  the GROUP BY clause, so you will get a random result from its possible
>  values in each group.

Exactly.  Before you run your query, run the following:

SET @@SQL_MODE='ONLY_FULL_GROUP_BY';

Now run your query again.  It will complain if you are not writing it
correctly, instead of just throwing some random data back at you and
assuming you will notice.

IMO this SQL_MODE setting is pretty much required for sanity :-)

Regards
Baron

-- 

Baron Schwartz, Senior Consultant, Percona Inc.
Tel: +1 888 401 3401 ext 507
24/7 Emergency Line +1 888 401 3401 ext 911
Our Services: http://www.percona.com/services.html
Our Blog: http://www.mysqlperformanceblog.com/
Thread
Incorrect results from sumJonathan Mangin5 Apr
  • Re: Incorrect results from sumJake Peavy5 Apr
  • Re: Incorrect results from sumJonathan Mangin5 Apr
    • Re: Incorrect results from sumPeter Brawley5 Apr
  • Re: Incorrect results from sumJonathan Mangin6 Apr
    • Re: Incorrect results from sumPerrin Harkins6 Apr
      • Re: Incorrect results from sumBaron Schwartz7 Apr