总和

Question

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

tblInvoice：

invcid,customerid,invoicedate 

tblInvcDetail：

ID,invcid,item,itemprice,itemquantity 

tblPay：

payid,invcid,paydate 

tblPayDetail：

payid,amount 

我需要创建其中userid给出invoiceid，invoicedate的列表，（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; 

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

样本数据：

tblInvoice：

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