Add amount from one row to amount in a different row with same primary key

I have a query where I would like to add the amount from one row to the amount of another row. Basically for any specific LLW, wherever WT_CPDS = ‘ASBESTOS’ and the first digit of FYP_NUM is not 5 or 6, id like to add the Obligation amount of this field to the obligation amount where WT_CPDS = ‘CONTIN’.

The field “MATCH” can also be used as a primary key since it is a unique identifier. I am using Oracle SQL Developer. Any help is much appreciated!

 SELECT LLW.LLW, LLW.FYP_NUM, WT_CPDS, SUM(OBLIGATION) AS OBLIGATION, LLW.LLW || OB.WT_CPDS AS MATCH
 FROM OBS_MASTER OB, LLW LLW
 WHERE LLW.LLW = OB.PROJECT AND  LLW.LLW = '049039' 
 GROUP BY LLW.LLW, LLW.FYP_NUM, WT_CPDS, LLW.LLW || OB.WT_CPDS
 ORDER BY WT_CPDS

 Actual Result:

   LLW      FYP_NUM     WT_CPDS     OBLIGATION      MATCH
  049039    4.090       ASBESTOS    14175.3         049039ASBESTOS
  049039    4.090       CONTIN      384812.65       049039CONTIN
  049039    4.090       DESIGN      21990.06        049039DESIGN
  049039    4.090       SCOPE       8209.68         049039SCOPE

 Expected Result:

  LLW       FYP_NUM     WT_CPDS     OBLIGATION      MATCH
  049039    4.090       ASBESTOS    14175.3         049039ASBESTOS
  049039    4.090       CONTIN      398987.95       049039CONTIN
  049039    4.090       DESIGN      21990.06        049039DESIGN
  049039    4.090       SCOPE       8209.68         049039SCOPE

Answer

You can use window functions:

SELECT LLW.LLW, LLW.FYP_NUM, WT_CPDS,
       (SUM(OBLIGATION) +
        (CASE WHEN WT_CPDS = 'CONTIN' AND FYP_NUM NOT IN (5, 6)
              THEN SUM(CASE WHEN WT_CPDS = 'ASBESTOS' THEN OBLIGATION ELSE 0 END) OVER (PARTITION BY  LLW)
              ELSE 0
        END) AS OBLIGATION,
       LLW.LLW || OB.WT_CPDS AS MATCH
FROM OBS_MASTER OB JOIN
     LLW LLW
     ON LLW.LLW = OB.PROJECT AND  LLW.LLW = '049039'
GROUP BY LLW.LLW, LLW.FYP_NUM, WT_CPDS, LLW.LLW || OB.WT_CPDS
ORDER BY WT_CPDS