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
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);