Summarising 2 Records into 1 1

[TimboA]

I have a query which looks something like this :-

select acct_no, type, sum(amount) TOTAL
from table1
group by acct_no, type;

The results look like this :-

ACCT_NO TYPE TOTAL

123456 DEBITS -2500
123456 CREDITS 4000

What I really want is this :-

ACCT_NO DEBIT TOTAL CREDIT TOTAL ACCOUNT BAL

123456 -2500 4000 1500

Can anyone help me with the syntax for this please ??

Thanks in advance.

 

[shajinair]

Use DECODE function like,

SELECT ACC_CODE,SUM(DECODE(TYPE,'DEBIT',AMOUNT,0)) DEBIT_TOT,SUM(DECODE(TYPE,'CREDIT',AMOUNT,0)) CREDIT_TOT
FROM T1
GROUP BY ACC_CODE