Re: Please help me write this query or function

On Tue, 11 Dec 2001 23:54:45 +1100 (EST)
Mr OCP <mr_ocp@yahoo.com> wrote:


> Hi
>
> We have a table like follows:
>
> id, account_name, amount, action_type
>
> The amount field has both debit and credit entires and
> action_type defines the type of transaction ( debit or
> credit), debit entries have negative '-' symbol before
> the amount.
>
> where there are multipble transaction for the same id,
> I need to write a query as under:
>
> select id, amount(credit), amount(debit), amount
> credit - amount debit from table where id is the same;
>
> Its making it difficult for me because the amount
> field has both debit and credit entries, your ideas,
> codes or sql will be much appreciated.
>

Would you care to use a UNION clause in a sub-select to divide the
amount column (with both debit and credit entries) into the different
fields ?  And if you have a large number of rows in the table,
you might create an index on the action_type and id columns.


A query example is:

drop table account;
drop index idx_account_id;
create table account (id           int4 not null,
                      account_name text default null,
                      amount       int4 not null,
                      action_type  varchar(10) not null,
                        check(action_type in ('credit','debit'))
                      );
create index idx_account_id_action on account (id, action_type);


insert into account values(1, '', 100, 'credit');
insert into account values(2, '', 200, 'credit');
insert into account values(2, '', 100, 'debit');
insert into account values(2, '', 200, 'debit');
insert into account values(3, '', 100, 'debit');



select t.id, sum(t.c) as credit, -sum(t.d) as debit,
       sum(t.c) - sum(t.d) as total
  from (select id, amount as c, 0 as d
               from account where action_type = 'credit'
        union all
        select id, 0, amount
               from account where action_type = 'debit'
        ) as t
  group by t.id




Regards,
Masaru Sugawara