两个表中两个字段的总和

Question

我在数据库中有四个表，如下所示：

tblInvoice:：

 invcid,customerid,invoicedate

tblInvcDetail：

ID,invcid,item,itemprice,itemquantity

tblPay：

payid,invcid,paydate

tblPayDetail：

payid,amount

我需要在给出用户标识的地方创建发票清单，发票日期，（itemprice * itemquantity的总和），（金额的总和）的列表。我试过这个查询：

SELECT tblinvoice.invcid,
       tblinvoice.invcdate,
       Sum(tblinvcdetail.itemprice * tblinvcdetail.itemquantity) AS SumOfInvoice,
       Sum(tblpaydetail.amount) AS SumOfAmount
FROM   ((tblinvoice
         LEFT JOIN tblpay
           ON tblinvoice.invcid = tblpay.invcid)
        LEFT JOIN tblinvcdetail
          ON tblinvoice.invcid = tblinvcdetail.invcid)
       LEFT JOIN tblpaydetail
         ON tblpay.payid = tblpaydetail.payid
GROUP  BY tblinvoice.invcid,
          tblinvoice.invcdate;

但是结果不是很正确，请帮助我。非常感谢。

样本数据：

tbl发票：

invcid   customerid invcdate        |invcsum(manualy calculated)
18      8             6/30/2012     |$140,000
39      8             7/12/2012     |$170,000
40      8             7/12/2012     |$80,000
43      8             7/14/2012     |$80,000
44      8             7/14/2012     |$80,000
45      8             7/15/2012     |$700,000
46      8             7/17/2012     |$180,000

tblInvcDetail：

ID  invccid  itemname       itemprice   itemquantity
19  18              X           $70,000     2
92  39              Y           $80,000     1
93  39              Z           $90,000     1
94  40              Y           $80,000     1
97  43              Y           $80,000     1
98  44              Y           $80,000     1
99  45              W           $700,000       1
100 46              Y           $80,000     1
101 46              U           $100,000       1

tblPay：

payid   invcid      paydate           |AmountSUM(Manually Calculated)     
35          18         7/11/2012    |$120,000
40          18         7/12/2012    |$147,000
41          40         7/12/2012    |$84,000
44          44         7/14/2012    |$84,000
46          45         7/15/2012    |$700,000

tblPayDetail：

payid       amount
35          $100,000
35          $20,000
40          $147,000
41          $84,000
44          $84,000
46          $700,000

最后查询结果为：

invcid  invcdate    SumOfInvoice    SumOfAmount
18      6/30/2012   $420,000.00     $267,000.00
39      7/12/2012   $170,000.00 
40      7/12/2012   $80,000.00      $84,000.00
43      7/14/2012   $80,000.00  
44      7/14/2012   $80,000.00      $84,000.00
45      7/15/2012   $700,000.00     $700,000.00
46      7/17/2012   $180,000.00

您可以在第一行（SumOfInvoice列）中看到计算错误，而其余的是正确的！

Answer 1

怎么样：

SELECT a.invcid,
       a.invcdate,
       a.sumofinvoice,
       b.sumofamount
FROM   (SELECT ti.invcid,
               ti.invcdate,
               SUM(td.itemprice * td.itemquantity) AS SumOfInvoice
        FROM   tblinvoice AS ti
               LEFT JOIN tblinvcdetail AS td
                      ON ti.invcid = td.invcid
        GROUP  BY ti.invcid,
                  ti.invcdate) a
       LEFT JOIN (SELECT tp.invcid,
                         SUM(tpd.amount) AS SumOfAmount
                  FROM   tblpay AS tp
                         LEFT JOIN tblpaydetail AS tpd
                                ON tp.payid = tpd.payid
                  GROUP  BY tp.invcid) b
              ON a.invcid = b.invcid