# 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?

```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;
```