Clever Geek Handbook

How to choose for the amount to reach a certain value

with 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 until the sum()

stock column reaches some value or slightly higher and the table will ORDER BY id ASC

.

For example, I want to select rows from a table until I have a stock sum of '50 ' , so the result is

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

the stock sum is now "55", which is the closest possible value above 50, and if we take the next row id:4

, the stock amount is above 50, and if we delete the row the id:3

value is 45, which is less than the stock that I want 50.

I can achieve this with PHP by selecting all lines and a loop to throw them away, but I think it would be a waste. Is there a way to do this at a lower level and let mysql do it for me with a query sql

?

Thanks and forgive me if I messed up something, I am new to programming

+3


source to share


1 answer


For this you need the total amount. 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);
+2


source






More articles:


All Articles