Mysql how to select SUM based on column other table

Question

Working on this app where I need make a query which seems to be impossible (after hours trying and trying).

Although, I have to admit, I am a bit rusty on the queries!

Anyway, lets assume there are these 3 tables:

Meal

id name
-- ----  
1  meal1

Food

id name  kcal kj
-- ----  ---- --
1  food1 200  200
2  food2 300  300

Meal_food

m_id f_id percentage_food_in_meal
---- ---- ----
1    1    0.60
1    2    0.40

What I am trying to select is: a meal, with the kcal, kj (and others) belonging to this meal based on the percentage.

As I tried, I already tried, but I can show you the closest I can get:

SELECT 
  m.*, 
  SUM(f.kj),    -- <- should be based on the percentage
  SUM(f.kcal)   -- <- should be based on the percentage
FROM 
  meal m, 
  meal_food mf, 
  food_nl f
WHERE 
  m.id = mf.meal_id
AND 
  f.id = mf.food_id
GROUP BY 
  m.id;

As the comment says: the based on the percentage is missing. Tried inner joins, outer joins, cross joins, sub queries, so I am wondering if it's even possible.

Hopefully my question about what I am trying to get is clear, if not I would be happy to try it again!

Update For example as output:

id name  kcal kj
-- ----  ---- --- 
1  meal1 240  240

To clarify: meal1 consists of 2 food (ingredients):

total meal kcal == 60% kcal food1 + 40% kcal food2
total meal kj == 60% kj food1 + 40% kj food2

I'm not sure what you mean by "based on the percentage". Could you show some sample output? — shmosel
– shmosel, Commented Dec 16, 2016 at 21:45

PeterHe · Accepted Answer · 2016-12-16 21:53:05Z

1

Not tested:

SELECT 
  m.id, MIN(m.name) AS meal_name, 
  SUM(f.kj*mf.percentage_food_in_meal),  
  SUM(f.kcal*percentage_food_in_meal)
FROM meal m 
INNER JOIN  meal_food mf
ON m.id = mf.meal_id
INNER JOIN food_nl f
ON f.id = mf.food_id
GROUP BY m.id;

answered Dec 16, 2016 at 21:53

PeterHe

2,7861 gold badge11 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jim Vercoelen Over a year ago

Holly F, how did you? It seems to be correct! Jesus so simple, BIG THANKS

Collectives™ on Stack Overflow

Mysql how to select SUM based on column other table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related