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?

回答1:

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;

回答2:

Try:

SELECT c.CaseNo, 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, i.InvFees