从多个表中获取总和 [英] Obtaining Sums from Multiple Tables

问题描述

我在单个 SQL 语句中从多个表中提取 Sums 时遇到问题.

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

我有三个表 tblCases、tblTimesheetEntries 和 tblInvoicestblCases 和另外两个表之间存在一对多的关系.

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

我目前正在使用以下 SQL 语句

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;

但是，这似乎与发票金额重复.例如，如果一个案例中只有一张发票，但假设有 4 个时间表条目，它会计算 4 倍发票金额作为该表的总和.

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.

如果我取消分组并运行以下 SQL:

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;

我可以看到这是因为发票金额在所有 4 行中重复，例如

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

所以发票总额是发票 001 金额的四倍.

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; 

这篇关于从多个表中获取总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！