Hi all
May be it is to easy to answer what i am trying to do now but my brain has realy stop and i need your opinions. I have a account table that has values as below
| Account | Fee | OBJ | type | unit | year |
|---|
| 1 | 1500 | 196 | AA | 130 | 20 |
| 1 | 2000 | 196 | AA | 130 | 20 |
| 1 | 2000- | 196 | AA | 130 | 20 |
| 1 | 1000- | 196 | AA | 130 | 20 |
| 2 | 3000 | 196 | AA | 130 | 20 |
| 2 | 4500- | 196 | AA | 130 | 20 |
So i can get debit and receiveable with two different sql query like in below but i can't get the debit and reciveable with sum in one row and sql. Please give me some opinion to go on.
Thanks
SELECT
TO_CHAR(TRIM(account)) Account_no,
(select accountname from addressbook where addid=TO_CHAR(TRIM(account))) accountname,
sum(fee/100) debit
FROM
account tb1
where
obj in ('196') and tb1.type='AA' and TO_CHAR(TRIM(unit))='130'
and year='20'
and fee>0
group by
account
order by
account;
SELECT
TO_CHAR(TRIM(account)) Account_no,
(select accountname from addressbook where addid=TO_CHAR(TRIM(account))) accountname,
sum(fee/100) reciveable
FROM
account tb1
where
obj in ('196') and tb1.type='AA' and TO_CHAR(TRIM(unit))='130'
and year='20'
and fee<0
group by
account
order by
account;