i am trying to command mysql to sum until it reaches certain value
from my table 'purchase', for each 'sid' starting from the last row, i need sum of 'pqty' until the result equals a value from string (but to try i've given a certain value).
let me define with the rows from my table---
the rows for 'sid=1' from 'purchase' are like this---
date pqty prate pamt 2014/04/29 5 38000 190000 2014/05/04 1 38000 38000 2014/05/13 20 35000 700000 2014/05/19 1 38000 38000
from this row, starting from the last row i want to 'sum(pqty) until it reaches 19(for now). it is achieved from adding last 2 rows(for 19). and stop sum here and return valus or sum of 'pqty', 'prate' and 'pamt'. to achieve this i tried the following
SELECT date, pqty, @total := @total + pqty AS total FROM (purchase, (select @total :=0) t) WHERE @total<19 AND sid = $sid ORDER BY date DESC
but it's not working. please guide me through this. also suggest something else if this is not the good technique for my purpose.
thankz in advance.....