两个表中两个字段的总和 [英] sum of two field in two table

问题描述

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

I have four tables in the database as follows:

tbl发票:

 invcid,customerid,invoicedate

tblInvcDetail:

ID,invcid,item,itemprice,itemquantity

tblPay:

payid,invcid,paydate

tblPayDetail:

payid,amount

我需要在提供用户ID的地方创建发票清单，发票日期，(项目价格之和*数量)，(金额之和)的列表. 我尝试了以下查询:

I need to create a list of invoiceid, invoicedate, (sum of itemprice*itemquantity), (sum of amount) where userid is given. I tried this query:

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; 

但是结果不是很正确 请帮我. 非常感谢.

But the result is not quite correct Please help me. Thanks a lot.

样本数据:

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列)中看到计算错误. 其余的都是正确的！

You can see that the calculation is wrong in the first row (SumOfInvoice column) and the rest is correct!

推荐答案

怎么样:

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 

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