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; 

Need Your Help

Where should I store settings for my javascript program?

javascript global

-- Full disclosure -- this is homework, and this is my capstone project. --

SortedSet<T> need to use different sort and equality criteria

c# asp.net sortedset

I have a class that was previously being used in a HashSet. This has now been changed so that the class is now used in a SortedSet, but the equality test no longer works as it did. I believe this i...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.