Wrong amount when I join the second table

Question

For the first time I ask you for help

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

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

My question is to use this query, I get the 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 think some lines are counted more than once, could you give me a hand?

EDIT

If I run a query to get the sum of the number 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 

but when I combine both requests, I get I false one, I expect July 2, 8362 qty versus 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 

Answer 1

Group entries per day using the 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 

Answer 2

One way to avoid this (assuming RDBMS support) is to

 WITH R AS (SELECT *, Sum(HeadCount) OVER (PARTITION BY date) AS SumHeadCount FROM Report) SELECT R.date, SumHeadCount, Sum(P.Quantity) AS SumQuantity FROM R JOIN Production P ON R.ReportID = P.ReportID GROUP BY R.date, SumHeadCount ORDER BY R.date 

Answer 3

It may be useful.

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