How to group records based on mask

Database

How to group records based on mask

How to group records like below:

 

ColA     ColB  Amount

------     ------    -----

123       111     10

123       1_1     5

321       222     20

 

into

 

ColA     ColB  Amount

------     ------    -----

123       1_1     15

321       222     20

 

Join on ColA and like ColB

 

I can join table to itself and get nearly what I need .....

 

ColA     ColB  Amount

------     ------    -----

123       111     10

123       1_1     5

123       1_1     10

321       222     20

 

Except not sure how to remove first row before grouping

 

1 REPLY

Re: How to group records based on mask

I did it by joining records with mask 

 

where REGEXP_SIMILAR(ColB, '[0-9]+_[0-9]+') = 1

 

to records without mask

 

where AND REGEXP_SIMILAR(ColB, '[0-9]*') = 1  

 

and then adding values

 

ZEROIFNULL(AA.Amount) + ZEROIFNULL(AB.Amount) SUM_Amount