Re: Please help me write this query or function

On Tue, 11 Dec 2001 23:54:45 +1100 (EST)
Mr OCP <mr_ocp(at)yahoo(dot)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