Thread: Calc
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
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
There have been several discussions on running totals on this list over the last couple of weeks. See the archives for possible solutions. -- -Josh BerkusAglio Database SolutionsSan Francisco
> 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. > If data from "View" without ID, how can I do? My View: trx_date | trx_time | descriptions | payment_method | debet | credit | creator ------------+----------+--------------------------------------+------------- ---+-------+----------+---------2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | Visa | 3800 | | middink2003-10-16 | 03:28:30 | Payment - Thank You | Visa | | 40000.00 | middink2003-10-08 | 18:17:40 | Payment - Thank You | Cash | | 50000.00 | middink
On Thursday 16 October 2003 10:37 am, Muhyiddin A.M Hayat wrote: > > 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. > > If data from "View" without ID, how can I do? > > My View: > > trx_date | trx_time | descriptions | > payment_method | debet | credit | creator > ------------+----------+--------------------------------------+---- >--------- ---+-------+----------+--------- > 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | > Visa > > | 3800 | | middink > > 2003-10-16 | 03:28:30 | Payment - Thank You | > Visa > > | | 40000.00 | middink > > 2003-10-08 | 18:17:40 | Payment - Thank You | > Cash > > | | 50000.00 The id field only keeps the transactions in the correct order so you can sum the previous transactions. You can do the same thing with your date and time fields (though using a single datetime field would be easier). A caution, though: you will have trouble if two transactions share the same date and time. Cheers, Steve
> > trx_date | trx_time | descriptions | > > payment_method | debet | credit | creator > > ------------+----------+--------------------------------------+---- > >--------- ---+-------+----------+--------- > > 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | > > Visa > > > > | 3800 | | middink > > > > 2003-10-16 | 03:28:30 | Payment - Thank You | > > Visa > > > > | | 40000.00 | middink > > > > 2003-10-08 | 18:17:40 | Payment - Thank You | > > Cash > > > > | | 50000.00 > > The id field only keeps the transactions in the correct order so you > can sum the previous transactions. You can do the same thing with > your date and time fields (though using a single datetime field would > be easier). A caution, though: you will have trouble if two > transactions share the same date and time. if i would like to pleaced ID(Virtual ID) for example number of record for each record, how to ? num_rec | trx_date | trx_time | credit | descriptions | payment_method | debet ---------+------------+----------+----------+------------------------------- -------------------------+----------------+------- 1 | 2003-10-09 | 21:55:02 | | Resto Biling : Rp. 13,800, Paid : Rp. 10,000 | Visa | 3800 2 | 2003-10-16 | 03:28:30 | 40000.00 | Payment | Visa | 3 | 2003-10-08 | 18:17:40 | 50000.00 | Payment | Cash |