title: SQL Union with CTE and Sum
body: <p>I'm a complete novice with SQL and have two queries down to the results I want, but I think I'm not using <code>UNION</code> correctly to combine them.  I had some help on this site earlier with a portion of the query.
  Below is a shortened version of what I have.</p>
  
  <pre><code>WITH RemoveData
       AS (SELECT a.PLANT_NO,
                  a.ALLOC_WHDV_VOL,
                  a.KW_CTR_REDELIVERED_HV,
                  a.MTR_NO,
                  a.MTR_SFX,
                  a.TRNX_ID,
                  a.REC_STATUS_CD,
                  MAX(a.ACCT_DT) ACCT_DT
           FROM   GasStmt a
                  INNER JOIN SettleSum c
                    ON a .= c.TRNX_ID
                       AND a.MTR_NO
                           ||a.MTR_SFX = c.MTR_NO
                                         ||c.MTR_SFX
                  LEFT OUTER JOIN FuelsCat d
                    ON a.TRNX_ID = d.TRNX_ID
                       AND a.MTR_NO
                           ||a.MTR_SFX = d.MTR_NO
                                         ||d.MTR_SFX
           WHERE  a.REC_STATUS_CD = 'RR'
           GROUP  BY a.PLANT_NO,
                     a.ALLOC_WHDV_VOL,
                     a.KW_CTR_REDELIVERED_HV,
                     a.MTR_NO,
                     a.MTR_SFX,
                     a.TRNX_ID,
                     a.REC_STATUS_CD
           HAVING COUNT(a.REC_STATUS_CD) &gt; 2) 
  SELECT plant_no                   "PlantNumber",
         SUM(-a.ALLOC_WHDV_VOL)     "PlantStandardGrossWellheadMcf",
         SUM(KW_CTR_REDELIVERED_HV) "KeepWholeResidueMMBtu"
  FROM   RemoveData a
  GROUP  BY plant_no
  UNION
  SELECT a.PLANT_NO            "PlantNumber",
         SUM(a.ALLOC_WHDV_VOL) "PlantStandardGrossWellheadMcf",
         SUM(CASE
               WHEN a.REC_STATUS_CD = 'RR' THEN -a.KW_CTR_REDELIVERED_HV
               ELSE a.KW_CTR_REDELIVERED_HV
             END)              "KeepWholeResidueMMBtu"
  FROM   GasStmt a
         INNER JOIN SettleSum c
           ON a.TRNX_ID = c.TRNX_ID
              AND a.MTR_NO
                  ||a.MTR_SFX = c.MTR_NO
                                ||c.MTR_SFX
         LEFT OUTER JOIN FuelsCat d
           ON a.TRNX_ID = d.TRNX_ID
              AND a.MTR_NO
                  ||a.MTR_SFX = d.MTR_NO
                                ||d.MTR_SFX
         LEFT OUTER JOIN (SELECT MTR_NO,
                                 MTR_SFX,
                                 TRNX_ID,
                                 REC_STATUS_CD,
                                 MAX(ACCT_DT) ACCT_DT
                          FROM   GasStmt
                          WHERE  REC_STATUS_CD = 'RR'
                          GROUP  BY MTR_NO,
                                    MTR_SFX,
                                    TRNX_ID,
                                    REC_STATUS_CD
                          HAVING COUNT(TRNX_ID) &gt; 1) b
           ON a.MTR_NO = b.MTR_NO
              AND a.TRNX_ID = b.TRNX_ID
              AND a.Rec_Status_Cd = b.REC_STATUS_CD
              AND a.Acct_Dt = b.ACCT_DT
  WHERE  TO_CHAR(a.PROD_DT, 'YYYY') &gt;= TO_CHAR(ADD_MONTHS(SYSDATE, -36), 'YYYY')
         AND a.STATUS_UNIT_TM_CD = 'M'
         AND b.MTR_NO IS NULL
  GROUP  BY a.PLANT_NO 
  </code></pre>
  
  <p>The above query returns:</p>
  
  <pre><code>PlantNumber | PlantStandardGrossWellheadMcf    |KeepWholeResidueMMBtu
  2           | 0                                | -3.96
  2           | 80                               |  100
  43          | 150                              |  0
  133         | 75                               |  0
  </code></pre>
  
  <p>If I run the first query with the CTE by itself, here's what I get:</p>
  
  <pre><code>PlantNumber | PlantStandardGrossWellheadMcf  |KeepWholeResidueMMBtu
  2           | 0                              |-3.96
  </code></pre>
  
  <p>Running the second query by itself, here's an example of sample data I get:</p>
  
  <pre><code>PlantNumber | PlantStandardGrossWellheadMcf    |KeepWholeResidueMMBtu
  2           | 80                               |  100
  43          | 150                              |  0
  133         | 75                               |  0
  </code></pre>
  
  <p>My intended result is to combine the data based on plant number.  Can that be done with UNION or am I doing something wrong?  This is what I'm hoping to achieve:</p>
  
  <pre><code>PlantNumber | PlantStandardGrossWellheadMcf    |KeepWholeResidueMMBtu
  2           | 80                               |  96.04
  43          | 150                              |  0
  133         | 75                               |  0
  </code></pre>