[SOLVED] How to select multiple items from a table based on certain cumulative amount?

Issue

I have a table name "foods", there have various foods with different calories. For example bellow:

Table: foods

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 

Solution

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.

Output:

id  name    calories
----------------------
1   beef    200
2   tomato  400

Fiddle Demo here

Answered By – Giorgos Betsos

Answer Checked By – Timothy Miller (BugsFixing Admin)