sum until sum equals n value

[funbinod]

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.....

[justsomeguy]

If you're going to add logic like that, I think you need to use a stored procedure. You could also return all records and do the sum in PHP.

[funbinod]

thank u @justsomeguy! but i know nothing about stored procedure. can u please tell me where can i read this with step by step explanation!?

[davej]

See...

 

http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html