pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCalc
6+ messages / 3 participants
[nested] [flat]
* Calc
@ 2003-10-15 19:59 Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-15 20:31 ` Re: Calc Steve Crawford <scrawford@pinpointresearch.com>
2003-10-15 22:15 ` Re: Calc Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 6+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-10-15 19:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calc
2003-10-15 19:59 Calc Muhyiddin A.M Hayat <middink@indo.net.id>
@ 2003-10-15 20:31 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-10-16 17:37 ` Re: Calc Muhyiddin A.M Hayat <middink@indo.net.id>
1 sibling, 1 reply; 6+ messages in thread
From: Steve Crawford @ 2003-10-15 20:31 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calc
2003-10-15 19:59 Calc Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-15 20:31 ` Re: Calc Steve Crawford <scrawford@pinpointresearch.com>
@ 2003-10-16 17:37 ` Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-16 17:35 ` Re: Calc Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 6+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-10-16 17:37 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-sql
> 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 | middink
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calc
2003-10-15 19:59 Calc Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-15 20:31 ` Re: Calc Steve Crawford <scrawford@pinpointresearch.com>
2003-10-16 17:37 ` Re: Calc Muhyiddin A.M Hayat <middink@indo.net.id>
@ 2003-10-16 17:35 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-10-17 01:38 ` Re: Calc Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 1 reply; 6+ messages in thread
From: Steve Crawford @ 2003-10-16 17:35 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calc
2003-10-15 19:59 Calc Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-15 20:31 ` Re: Calc Steve Crawford <scrawford@pinpointresearch.com>
2003-10-16 17:37 ` Re: Calc Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-16 17:35 ` Re: Calc Steve Crawford <scrawford@pinpointresearch.com>
@ 2003-10-17 01:38 ` Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 0 replies; 6+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-10-17 01:38 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-sql
> > 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 |
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Calc
2003-10-15 19:59 Calc Muhyiddin A.M Hayat <middink@indo.net.id>
@ 2003-10-15 22:15 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-10-15 22:15 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; pgsql-sql
There have been several discussions on running totals on this list over the
last couple of weeks. See the archives for possible solutions.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-10-17 01:38 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-15 19:59 Calc Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-15 20:31 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-10-16 17:37 ` Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-16 17:35 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-10-17 01:38 ` Muhyiddin A.M Hayat <middink@indo.net.id>
2003-10-15 22:15 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox