Re: Incorrect results from sum

["Jonathan Mangin" <jon.mangin@xxxxxxxxxxx>]

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx