title: T-SQL: Group two tables, then join
body: <p>I have two tables (OldVals and NewVals), both with the same format:</p>
  
  <pre><code>PK ProductID  int
  PK CustomerID int
     Value      decimal
  </code></pre>
  
  <p>Some sample data:</p>
  
  <p><strong>OldVals:</strong></p>
  
  <pre><code>ProductID   CustomerID  Value
  1           1000        10
  2           1000        20
  3           1000        30
  1           2000        40
  4           2000        50
  2           3000        60
  3           3000        70
  4           3000        80
  </code></pre>
  
  <p><strong>NewVals:</strong></p>
  
  <pre><code>ProductID   CustomerID  Value
  1           1000        50
  2           1000        60
  1           2000        70
  2           3000        80
  3           3000        90
  </code></pre>
  
  <p>I'm trying to query these two tables and obtain: CustomerID, Sum of values for each customer in OldVals, Sum of values for each customer in NewVals. ProductIDs are ignored.</p>
  
  <p>The result for the given sample data would be:</p>
  
  <pre><code>CustomerID  SumOld  SumNew
  1000        60      110
  2000        90      70
  3000        210     170
  </code></pre>
  
  <p>With this query:</p>
  
  <pre><code>select
      OldVals.CustomerID,
      sum(distinct OldVals.Value),
      sum(distinct NewVals.Value) 
  
  from
      OldVals full outer join NewVals on OldVals.CustomerID = NewVals.CustomerID
  
  group by OldVals.CustomerID, NewVals.CustomerID
  </code></pre>
  
  <p>I obtain the right result for the given sample data, but of course if "Value" field is the same for two rows of the same customer, then the query gives a wrong result, because rows with the same value (within a customer) are summed only once.</p>
  
  <p>Just to clarify, If I replace the first row of OldVals with:</p>
  
  <pre><code>ProductID   CustomerID  Value
  1           1000        20
  </code></pre>
  
  <p>the result should be:</p>
  
  <pre><code>CustomerID  SumOld  SumNew
  1000        70      110
  ...
  </code></pre>
  
  <p>but I obtain:</p>
  
  <pre><code>CustomerID  SumOld  SumNew
  1000        50      110
  </code></pre>
  
  <p>Any advice?</p>
  
  <p>Thank you!</p>