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

Recommended Answers

but it's not working

What exactly isn't working?

Jump to Post

All 2 Replies

but it's not working

What exactly isn't working?

it is just descending all rows, beyond my wish. i just wish to select last 2 rows descending (to get 'pqty' at least 19 for now. this value can change with the further transection).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.