Jonathan Mangin schrieb:
I'm trying to total certain nutrients consumed on a given date
(though I've removed date temporarily).
You'll see I have three items (in two meals) in itemized,
and two meal totals in simple.
mysql> select id, item, carb from my_menu where id in (10, 11, 22);
+----+-----------------------------+-------+
| id | item | carb |
+----+-----------------------------+-------+
| 10 | apples, w/skin, raw | 0.138 |
| 11 | bananas, raw | 0.228 |
| 22 | bread, Arnold Natural Wheat | 0.500 |
+----+-----------------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from itemized;
+----+------------+-------------+---------+-------------+----------+
| id | date | time_of_day | uid | personal_id | units |
+----+------------+-------------+---------+-------------+----------+
| 3 | 2008-04-01 | 06:15:00 | jmangin | 10 | 167.0000 |
| 7 | 2008-04-01 | 12:30:00 | jmangin | 11 | 52.0000 |
| 6 | 2008-04-01 | 12:30:00 | jmangin | 22 | 36.0000 |
+----+------------+-------------+---------+-------------+----------+
3 rows in set (0.01 sec)
mysql> select * from simple;
+----+------------+-------------+---------+------+---------+------+
| id | date | time_of_day | uid | carb | protein | fat |
+----+------------+-------------+---------+------+---------+------+
| 1 | 2008-04-01 | 12:05:00 | jmangin | 85.0 | 10.0 | 2.3 |
| 2 | 2008-04-01 | 18:30:00 | jmangin | 80.4 | 10.0 | 10.0 |
+----+------------+-------------+---------+------+---------+------+
2 rows in set (0.01 sec)
mysql> select sum(my_menu.carb*units) from itemized left join my_menu on
personal_id=my_menu.id;
+-------------------------+
| sum(my_menu.carb*units) |
+-------------------------+
| 52.9020 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select sum(carb) from simple;
+------------+
| sum(carb) |
+------------+
| 165.4 |
+------------+
1 row in set (0.01 sec)
select
round(sum(my_menu.carb * units) + sum(simple.carb),2)
from itemized inner join simple using (uid)
left join my_menu on itemized.personal_id = my_menu.id;
Instead of 218.3 this returns 602, which is
(52.9 * 2 items in simple) + (165.4 * 3 items in itemized).
Is it possible to get correct totals some other way with
this table structure? Or explain why this is wrong?
use UNION
http://dev.mysql.com/doc/refman/5.0/en/union.html
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
