我加入第二张桌子时的总和不正确 [英] Incorrect sum when I join a second table

问题描述

这是我第一次寻求您的帮助，

This is the first time I ask for your help,

实际上，我必须创建一个查询，并为此做了一个类似的示例.我有两个桌子

Actually I have to create a query, and did a similar example for it. I have two tables,

Report (ReportID, Date, headCount)
Production(ProdID, ReportID, Quantity)

我的问题是使用此查询，我得到了错误的结果，

My question is using this query, I get a wrong result,

SELECT    
    Report.date, 
    SUM(Report.HeadCount) AS SumHeadCount, 
    SUM(Production.Quantity) AS SumQuantity
FROM         
    Report 
INNER JOIN
    Production ON Report.ReportID = Production.ReportID
GROUP BY
    Date
ORDER BY
    Date

我想有些行已被计算不止一次，您能帮我一下吗?

I guess some rows are being counted more than once, could you please give me a hand?

编辑

如果我运行查询以获取按天分组的总人数，我将得到:

if i run a query to get a sum of headcount grouped by day, I get:

  date        Headcount
7/2/2012    1843
7/3/2012    1802
7/4/2012    1858
7/5/2012    1904

对于生产数量我也得到:

also for Production Qty I get:

2012-07-02  8362
2012-07-03  8042
2012-07-04  8272
2012-07-05  9227

但是当我将两个查询合并时，我得到了一个错误的查询，我期望在7月2日8362的数量为1843，但是我得到:

but when i combine the both queries i get i false one, i expect on 2 july 8362 qty against 1843, but i get:

 day      TotalHeadcount    totalQty
7/2/2012    6021    8362
7/3/2012    7193    8042
7/4/2012    6988    8272
7/5/2012    7197    9227

推荐答案

每个日期使用以下记录分组记录

Group records per date using following

SELECT ReportSummary.ReportDate, SUM(ReportSummary.SumHeadCount) AS SumHeadCount, SUM(ProductionSummary.SumQuantity) AS SumQuantity
FROM
(
  SELECT Report.ReportDate, SUM(Report.HeadCount) AS SumHeadCount
  FROM Report 
  GROUP BY Report.ReportDate
) AS ReportSummary
INNER JOIN
(
  SELECT Report.ReportDate, Sum(Production.Quantity) AS SumQuantity
  FROM Production
  INNER JOIN Report 
  ON Report.ReportID = Production.ReportID
  GROUP BY Report.ReportDate
) AS ProductionSummary
ON ReportSummary.ReportDate = ProductionSummary.ReportDate
GROUP BY ReportSummary.ReportDate
ORDER BY ReportSummary.ReportDate

这篇关于我加入第二张桌子时的总和不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！