Incorrect sum when I join a second table

Question

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?

EDIT

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

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

Answer 1

One way of avoiding this (subject to RDBMS support) would be

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