Power of case statement into SQL - Multiple result in Single Query

Query Statement : Having a table "Acct_stat" with column "Trans_type"(Dr or Cr) , "Amount" Pls write a single query which should return sum of "Debit" , "Credit" and "Total Balance" of each account.

Answer : We can use a case statement as best choice for such queries like following with group by Account:

Let's go with following SQL

Drop table Acct_stat;
create table Acct_stat (Acount_No varchar2(10),TRANS_TYPE VARCHAR2(2),AMOUNT NUMBER);

insert into Acct_stat values('1' , 'DR' , 100);
insert into Acct_stat values('1' , 'CR' , 100);
insert into Acct_stat values('1' , 'DR' , 100);
insert into Acct_stat values('2' , 'CR' , 100);
insert into Acct_stat values('2' , 'CR' , 100);
insert into Acct_stat values('2' , 'DR' , 100);
insert into Acct_stat values('3' , 'DR' , 100);
insert into Acct_stat values('3' , 'CR' , 100);

SELECT Acount_No ,
SUM (CASE WHEN Trans_type = 'DR'
THEN AMOUNT ELSE null END) DB_SUM,
SUM (CASE WHEN Trans_type = 'CR'
THEN AMOUNT ELSE null END) CR_SUM,
SUM (AMOUNT) TOT_BAL
FROM ACCT_STAT GROUP BY (Acount_No);

and Result is :
Acount_No DB_SUM CR_SUM TOT_BAL
1 200 100 300
2 100 200 300
3 100 100 200


Cheers

Kapil