title: Grouping [Union All] result from multiple columns SQL query
body: <p>I have a deal-table as below (more columns exists). I have been trying to use [Union] in my SQL query but when the value in dealContributor1ACV is the same as in dealContributor2ACV, the sum seems to exclude it. (dcA)</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>dealContributor1</th>
  <th>dealContributor2</th>
  <th>dealContributor1ACV</th>
  <th>dealContributor2ACV</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>dcA</td>
  <td>dcD</td>
  <td>50000</td>
  <td>50000</td>
  </tr>
  <tr>
  <td>dcC</td>
  <td>dcA</td>
  <td>20000</td>
  <td>12000</td>
  </tr>
  <tr>
  <td>dcA</td>
  <td>dcC</td>
  <td>10000</td>
  <td>17000</td>
  </tr>
  <tr>
  <td>dcD</td>
  <td>dcB</td>
  <td>15000</td>
  <td>11000</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Looking for a query resulting in the below structure.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>dealContributor</th>
  <th>dealContributorACV</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>dcA</td>
  <td>72000</td>
  </tr>
  <tr>
  <td>dcB</td>
  <td>11000</td>
  </tr>
  <tr>
  <td>dcC</td>
  <td>37000</td>
  </tr>
  <tr>
  <td>dcD</td>
  <td>65000</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Here is what I am using now.</p>
  <pre><code>SELECT s.deal_contributer1 as 'dealContributor',s.deal_contributer1_acv as 'goalCompletionACV', GETDATE() as 'date2a'
  FROM [dbo].[DynPSCsPersonGoals_20-21] g inner join [dbo].[HubspotDeal] s on g.email = s.deal_contributer1 WHERE s.Stage = 'closedwon' AND s.CloseDate &gt;= '2020-09-01' AND s.CloseDate &lt; '2021-01-01'
  UNION
  SELECT s.deal_contributer2 as 'dealContributor',s.deal_contributer2_acv as 'goalCompletionACV', GETDATE() as 'date2a'
  FROM [dbo].[DynPSCsPersonGoals_20-21] g inner join [dbo].[HubspotDeal] s on g.email = s.deal_contributer2 WHERE s.Stage = 'closedwon' AND s.CloseDate &gt;= '2020-09-01' AND s.CloseDate &lt; '2021-01-01'
  ORDER by dealContributor
  </code></pre>
  <p>Thanks in advance!</p>