title: Obtaining Sums from Multiple Tables
body: <p>I'm having an issue extracting Sums from multiple tables in a single SQL statement.</p>
  
  <p>I have three tables tblCases, tblTimesheetEntries and tblInvoices
  There is a one to many relationship between tblCases and each of the other two tables.</p>
  
  <p>I am using the following SQL statement at the moment</p>
  
  <pre><code>SELECT c.CaseNo, SUM(i.InvFees), SUM(t.Fees)
  FROM tblCases AS c
  INNER JOIN tblInvoices AS i ON c.CaseNo = i.CaseNo
  INNER JOIN tblTimesheetEntries AS t ON c.CaseNo = t.CaseNo
  GROUP BY c.CaseNo
  ORDER BY c.CaseNo;
  </code></pre>
  
  <p>However, this seems to duplicate the invoice amounts.  For example if there is only one invoice on a case, but say 4 timesheet entries, it calculates 4 x the invoice amount as the Sum for that table.</p>
  
  <p>If I take the grouping out and run the following SQL instead:</p>
  
  <pre><code>SELECT c.CaseNo, i.InvFees, t.Fees
  FROM tblCases AS c
  INNER JOIN tblInvoices AS i ON c.CaseNo = i.CaseNo
  INNER JOIN tblTimesheetEntries AS t ON c.CaseNo = t.CaseNo    
  ORDER BY c.CaseNo;
  </code></pre>
  
  <p>I can see that this is happening because the invoice amount is repeated in all 4 lines e.g.</p>
  
  <pre><code>Case 1001,  Inv 001  950.00,  TimeFees  250.00
  Case 1001,  Inv 001  950.00,  TimeFees  175.00
  Case 1001,  Inv 001  950.00,  TimeFees  225.00
  Case 1001,  Inv 001  950.00,  TimeFees  190.00
  </code></pre>
  
  <p>So the total of the invoices is four times the amount of Invoice 001.</p>
  
  <p>What I would like returning from the above data is a single summation line:</p>
  
  <pre><code>Case 1001,  Total Invoices 950.00,  Total TimeFees 840.00
  </code></pre>
  
  <p>How do I avoid this duplication in the summations?</p>