I have a table name "foods", there have various foods with different calories. For example bellow:
id name calories ----------------------- 1 beef 200 2 tomato 400 3 carrot 500 4 nuts 800
what I want: I want to select multiple foods from the table based on calorie amount when the sum of callories reach to a certain amount, I want to stop query there and echo the result. For example, when I will want 600 calories, result will show for the first two food 200+400=600. when I will want calorie limit will be 1000, it will take any two or more foods those calories amount reach to 1000 or near 1000.
I have tried with following code to make it ; but it does not work.
SELECT * FROM foods WHERE SUM(calories)=1000
You can use a variable in order to calculate cumulative sum:
SELECT id, name, calories FROM ( SELECT id, name, calories, @cumSum:= @cumSum + calories AS cumSum FROM foods, (SELECT @cumSum:=0) var ORDER BY id ) t WHERE cumSum <= 600
The subquery calculates the cumulative sum and stores its value in
@cumSum variable. The outer query can then use calculated column
cumSum to filter out results having a cumulative sum greater than 600.
id name calories ---------------------- 1 beef 200 2 tomato 400
Answered By – Giorgos Betsos
Answer Checked By – Timothy Miller (BugsFixing Admin)