MS Access Rounding Precision With Group By

Question

Why doesn't the average of the score of an employee of each month, when summed, equal the average of the employees score (ever)?

Average

SELECT Avg(r.score) AS rawScore
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))

Returns 80.737

---

Average By Month

SELECT Avg(r.score) AS rawScore, Format(submitDate, 'mmm yy') AS MonthText,  month(r.submitDate) as mm, year(submitDate) as yy
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))
GROUP BY month(r.submitDate), year(submitDate), Format(submitDate, 'mmm yy')
ORDER BY year(submitDate) DESC, month(r.submitDate) DESC

Returns

Average Score : Month 
81.000 : Oct 09 
80.375 : Sep 09 
82.700 : Aug 09 
83.100 : Jul 09 
75.625 : Jun 09

I know 80.737 is correct because I have tallied up the records by hand and done the average. But the average of this table (at 3 decimal places), is 80.56 which is too far off. Does group by mess with the rounding at each step?

Answer 1

I wouldn't expect the two results to be the same, for the simple reason that, if rounding is occurring, you're rounding five times in the monthly scores, and only once for the yearly.

That said, I would check the record counts also, and see if they jibe. It is possible given the formatting on the date and such, that a record or two is slipping through the cracks on the monthly queries.

Answer 2

An average of average values will not return the same result as a single average over all values, unless all the groups averaged have the same number of items.

If there are different numbers of employees rawScore each month it will be skewing your results.

Consider this example: if we calculate the average of the numbers 1 through 10 the average is 5.5.

Calculating the average of the numbers from 1 through 5 the average is 3, and of 6 through 10 is 8. Both groups have 5 items so the average of 3 and 8 = 5.5.

However, if you take the first average as 1 and 2 = 1.5, and the second average as 3 through 10 = 6.5, then average 1.5 and 6.5 gives 4. This is skewed because the first group has 2 items, and the second has 8.

In addition to this will be the cumulative effects of rounding that Robert Harvey noted.