CODEWITHSUNDEEP
sqloracle-databasegroup-byoracle11g
Bala S
Bala S

Reputation: 523

How to use group by function in oracle

Below is the my base table

enter image description here

I need to create view like below format based on my base table. Here Deposit_Acc_Count is combination of (Type -> SB,RD, FD) and Loan_Acc_Count is (Type ->LO) same as for amount.

enter image description here

Can anyone help me to achieve this.

Upvotes: 0

Views: 28

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use conditional aggregation while grouping by br_code and cif columns such as

SELECT br_code, cif,
       COUNT(CASE WHEN type IN ('SB','RD', 'FD') THEN Amt END) AS deposit_acc_count, 
       SUM(CASE WHEN type IN ('SB','RD', 'FD') THEN Amt ELSE 0 END) AS deposit_acc_amt, 
       COUNT(CASE WHEN type = 'LO' THEN Amt END) AS loan_acc_count, 
       SUM(CASE WHEN type = 'LO' THEN Amt ELSE 0 END) AS loan_acc_amt, 
       COUNT(*) AS total_count,
       SUM(amt) AS total_amt                   
  FROM t
 GROUP BY br_code, cif

Demo

Upvotes: 2

Related Questions