CODEWITHSUNDEEP
mysqlsql
Accountant م
Accountant م

Reputation: 7483

How to select till the sum reach some value

having this table

#table stock
+-------+----------+
|  id   |   stock  |
+-------+----------+
|   1   |    20    |
+-------+----------+
|   2   |    25    |
+-------+----------+
|   3   |    10    |
+-------+----------+
|   4   |    20    |
+-------+----------+
#note: this is an arbitrary random data

How can I keep selecting rows from the table till the sum() of the stock column reaches some value or a little higher , and the table is ORDER BY id ASC.

For example I want to select rows from the table till I have sum of stock '50' , so the result will be

#result 3 rows
+-------+----------+
|  id   |   stock  |
+-------+----------+
|   1   |    20    |
+-------+----------+
|   2   |    25    |
+-------+----------+
|   3   |    10    |
+-------+----------+

the sum of stock now is '55' which is the closest possible higher value than '50' , and if we take the next row id:4 the sum of stock will be higher than 50 , and if we remove the row id:3 the value will be 45 which is less than the stock I want 50 .

I can achieve this with PHP by selecting all the rows and loop throw them, but I guess that will be a waste. Is there a possible way to do that on a lower level and let mysql do that for me by a sql query?

Thank you and forgive me if I messed something , I'm new to programming

Upvotes: 5

Views: 1329

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need a cumulative sum for this to work. One method uses variables:

select t.*
from (select t.*, (@sum := @sum + stock) as cume_stock
      from t cross join
           (select @sum := 0) params
      order by id 
     ) t
where cume_stock < 50 or (cume_stock >= 50 and cume_stock - stock < 50);

Upvotes: 5

Related Questions