List:General Discussion« Previous MessageNext Message »
From:Jonathan Mangin Date:April 6 2008 1:28am
Subject:Re: Incorrect results from sum
View as plain text  
Hi,

Here's the basic query I'm trying. It's supposed to return
the totals of each nutrient (carb, in this case) by date.
The data totals 218.31, but I get 190.80.

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;

I've tried different joins and a single date, with no change.
I assume a misunderstanding on my part, and not a bug.

CREATE TABLE `my_menu` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`category` tinyint(3) unsigned NOT NULL default '0',
`item` varchar(60) NOT NULL default '',
`uom` varchar(12) NOT NULL default '',
`carb` decimal(8,3) unsigned NOT NULL default '0.000',
PRIMARY KEY  (`id`),
UNIQUE KEY `cat_item` (`category`,`item`),
UNIQUE KEY `item_uom` (`item`,`uom`)
)

CREATE TABLE `itemized` (
`id` int(10) unsigned NOT NULL auto_increment,
`day_date` date NOT NULL default '0000-00-00',
`uid` varchar(14) NOT NULL default '',
`time_of_day` time NOT NULL default '00:00:00',
`personal_id` smallint(5) unsigned NOT NULL default '0',
`units` decimal(8,4) unsigned NOT NULL default '0.0000',
PRIMARY KEY  (`id`),
KEY `uid_date_time` (`uid`,`day_date`,`time_of_day`)
)

CREATE TABLE `simple` (
`id` int(10) unsigned NOT NULL auto_increment,
`day_date` date NOT NULL default '0000-00-00',
`uid` varchar(14) NOT NULL default '',
`time_of_day` time NOT NULL default '00:00:00',
`uid` varchar(14) NOT NULL default '',
`carb` decimal(4,1) unsigned default NULL,
`protein` decimal(4,1) unsigned default NULL,
`fat` decimal(4,1) unsigned default NULL,
PRIMARY KEY  (`id`),
UNIQUE KEY `uid_date_time` (`uid`,`day_date`,`time_of_day`)
)

insert my_menu
(id, category, item, uom, carb)
values
(NULL, '15', 'apples, w/skin, raw', 'gr', '0.138'),
(NULL, '15', 'bananas, raw', 'gr', '0.228'),
(NULL, '17', 'bread, Arnold Natural Wheat', 'gr', '0.500');

insert itemized
(id, day_date, uid, time_of_day, personal_id, units)
values
(NULL, '2008-4-01', 'me', '06:05:00', '1', '167'),
(NULL, '2008-4-01', 'me', '12:30:00', '3', '36'),
(NULL, '2008-4-01', 'me', '12:30:00', '2', '52');

insert simple
(id, day_date, uid, time_of_day, carb, protein, fat)
values
(NULL, '2008-4-01', 'me', '12:05:00', '85', '10', '2.3'),
(NULL, '2008-4-01', 'me', '18:30:00', '80.4', '10', '10');

Hope this is complete. Thanks alot,
Jon

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