# Obtaining Sums from Multiple Tables

I'm having an issue extracting Sums from multiple tables in a single SQL statement.

I have three tables tblCases, tblTimesheetEntries and tblInvoices There is a one to many relationship between tblCases and each of the other two tables.

I am using the following SQL statement at the moment

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;

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.

If I take the grouping out and run the following SQL instead:

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;

I can see that this is happening because the invoice amount is repeated in all 4 lines e.g.

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

So the total of the invoices is four times the amount of Invoice 001.

What I would like returning from the above data is a single summation line:

Case 1001, Total Invoices 950.00, Total TimeFees 840.00

How do I avoid this duplication in the summations?

## Answers

SELECT c.CaseNo, i.InvFees, t.Fees FROM tblCases AS c INNER JOIN (SELECT CaseNo, Sum(InvFees) AS InvFees FROM tblInvoices GROUP BY CaseNo) AS i ON c.CaseNo = i.CaseNo INNER JOIN (SELECT CaseNo, Sum(Fees) AS Fees FROM tblTimesheetEntries GROUP BY CaseNo) AS t ON c.CaseNo = t.CaseNo ORDER BY c.CaseNo;