title: SUM on LEFT OUTER JOIN on 2 different child tables
body: <p>I have the following table schema</p>
  
  <p><img src="https://i.stack.imgur.com/2McIS.png" alt="enter image description here"></p>
  
  <p>What I want to do is return all invoices with the following summarized totals</p>
  
  <ul>
  <li>Total of UnitPrice * Qty for all Line items (e.g. <code>SUM(UnitPrice * Qty)</code>)</li>
  <li>Total of Amount for all Additional Costs (e.g. <code>SUM(Amount)</code>)</li>
  </ul>
  
  <p>Given that an Invoice can exist without any Line Items or Additional Costs I thought all I would need to do here is use a <code>LEFT OUTER JOIN</code> i.e.</p>
  
  <pre><code>SELECT i.*, SUM(li.UnitPrice * li.Qty) As [Sub Total], SUM(ac.Amount) As [AdditionalCosts]
  FROM Invoice i
  LEFT OUTER JOIN LineItem li ON li.InvoiceId = i.Id
  LEFT OUTER JOIN AdditionalCost ac ON ac.InvoiceId = i.Id
  GROUP BY i.Id
  </code></pre>
  
  <p>However, the problem is that if both sub tables are of varied length (e.g. I have 4 line items, but only 1 additional cost) the data for the additional cost is repeated across the extra line item rows (and vice versa), you can verify this by removing the <code>GROUP BY</code>. </p>
  
  <p>So effectively what happens is for the following record</p>
  
  <pre><code>Invoice
  -------
  400001
  
  LineItem
  ---------
  400001 | 2000 | 100 | 1
  400001 | 2001 | 50  | 2
  400001 | 2002 | 10  | 10
  400001 | 2003 | 20  | 5
  
  AdditionalCost
  --------------
  1 | 400001 | 30
  2 | 400001 | 70
  </code></pre>
  
  <p>My result set would look like</p>
  
  <pre><code>Id    | Sub Total  | Additional Costs
  --------------------------------------
  40001 | 800        | 400 &lt;-- this should be 100
  </code></pre>
  
  <p>How can I calculate the <code>SUM</code> of each table independently and combine them into a single master record? </p>