On Wednesday 15 October 2003 12:59 pm, Muhyiddin A.M Hayat wrote: > Dear all, > > I have below table > > +-----+--------------------+---------------------+ > > | id | db |cr | > > +-----+--------------------+---------------------+ > > |1 | | 200 | > |2 | 100 | | > |3 | 100 | | > |4 | 150 | | > |5 | | 200 | > > I Would like to calc that balance, and look like > > +-----+--------------------+---------------------+-----------+ > > | id | db |cr | > | bal | > > +-----+--------------------+---------------------+-----------+ > > |1 | | 200 > | |200 | 2 | 100 | > | |100 | 3 | 100 | > | |0 | 4 | 150 > | | |-150 | 5 | > | | 200 |50 | > > What can I do to get result like that
Something like this: select id,db,cr,(select sum(cr-db) from calc sub where sub.id <= calc.id) from calc; This of course assumes that ID indicates the correct order of the entries and it will blow up if you allow nulls for cr or db (which you shouldn't since that would literally be interpreted as "they withdrew 10 and deposited an unknown amount"). If you have null values already and they should be interpreted as 0 just do this: select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0)) from calc sub where sub.id <= calc.id) from calc; I assume no responsibility for potential lack of scalability of this query. :) It's quite possible a faster solution exists - we'll see what comes up on the list. Cheers, Steve ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
