How to merge two tables together given a specific criteria - Oracle SQL

Question

I have a table that looks like this:

lob|desc|cnt
dogs,declined,5
cats,declined,5
rats,declined,8
dogs,failed,2

I am trying to create a new table that looks like this:

lob|cnt|failed_cnt
dogs,5,2
cats,5,0
rats,8,0

Essentially, I am merging results together by the column LOB so there is no duplicate records. LOB values that desc column value is failed, merge them together and take that cnt and put it in the new column ""failed_cnt". If that makes sense.

I wrote some SQL code that puts null values but there are stills duplicates. Here is my output:

 lob|cnt|failed_cnt
  dogs,5,0
  cats,5,0
  rats,8,0
  dogs 0,2

Here is my code:

SELECT 
    lob,
    CASE 
        WHEN CNT <> 0 AND desc not like 'Failed%' 
            THEN CNT
            ELSE 0
    END AS CNT,
    CASE  
        WHEN CNT <> 0 AND desc LIKE 'Failed%' 
            THEN CNT
            ELSE 0
    END AS FAILED_CNT
FROM 
    table1

Any ideas or suggestions?

Answer 1

Use aggregation:

select lob,
       sum(case when descr = 'declined' then cnt else 0 end) as declined,
       sum(case when descr = 'failed' then cnt else 0 end) as failed
from t
group by lob;