From: Steve Crawford <scrawford@pinpointresearch.com>
To: Muhyiddin A.M Hayat <middink@indo.net.id>
To: pgsql-sql@postgresql.org
Subject: Re: Calc
Date: Wed, 15 Oct 2003 13:31:22 -0700
Message-ID: <200310151331.22516.scrawford@pinpointresearch.com> (raw)
In-Reply-To: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
References: <002e01c39356$d5885ca0$1f00a8c0@middinkcomp>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, middink@indo.net.id
Subject: Re: Calc
In-Reply-To: <200310151331.22516.scrawford@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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