两个表中两个字段的总和
我在数据库中有四个表,如下所示:
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列)中看到计算错误,而其余的是正确的!
-
怎么样:
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