Oracle SQL to Select rows based on case

Question

I have a table with the following fields

ID          Category    AgingSet    Amount
K-218660115 02          STD         -159
K-218660115 03          STD           60
K-218660115 04          STD           90
K-218660115 05          STD            6
K-218660115 06          BDSTD        156

My sql select should return the last row i.e.

K-218660115 06          BDSTD        156

if STD aging set is non zero. Had all the STD aging set been set to 0 , my SQL select would return

K-218660115 02          BDSTD             0

Any help is appreciated.

question from:https://stackoverflow.com/questions/65869499/oracle-sql-to-select-rows-based-on-case

Answer 1

The logic seems a little hard to follow. You seem to want conditional logic based on whether or not the sum is 0 for the 'STD' values.

This should return what you want on the data you provided:

select id, category, 'BDSTD' as agingSet, amount
from (select t.*,
             row_number() over (partition by id order by category) as seqnum,
             sum(case when agingSet = 'Std' and amount <> 0 then 1 else 0 end) over (partition by id) as num_not_zero
      from t
     ) t
where (num_not_zero = 0 and seqnum = 1) or
      (num_not_zero > 0 and agingSet = 'BDSTD');