CODEWITHSUNDEEP
sqloracle-database
Sandysql
Sandysql

Reputation: 11

How to write a sql to select records with specific conditions

Need to select from a same column based on specific conditions from a same table

I tried using max case when status =‘OP’ then tot_amountelse 0 Max case when status =‘PR’ and curr_flag =‘Y’ thentot_amount else 0

Below is the table 

ID Type Status tot_Amount  curr_flag
1   Null OP      100           N
1.  F.   PR     60             N
1.  H.   PR.     0.            Y

Expected output

ID Type.  TotalAmt Bal-Amt 
1.  H.    100.     0

Upvotes: 0

Views: 57

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want conditional aggregation :

select id, 
       max(case when curr_falg = 'Y' then Type end) as Type,
       sum(case when Status = 'OP' then tot_Amount else 0 end) as TotalAmt,
       sum(case when Status = 'PR' and curr_falg = 'Y' then tot_Amount else 0 end) as Bal_Amt 
from table t
group by id;

Upvotes: 1

Related Questions