AVG function returns 0.9999

This one is a very odd bug! 😐

For some reason, the AVG function from MySQL is always returning the value 0.9999 instead of the average value. This has been experienced on MySQL 5.5.12 hosted on Amazon RDS (Relational Database Service).
However, the exact same query executed on MySQL 5.1.28 is returning the right values.

Why is that? Is it a bug in MySQL 5.5.12?
I did a search on internet and I couldn’t find anything about it. So to be honest, I am not sure what is this bug or even if MySQL is aware of it.

Anyway, if you encounter the same problem, you can simply replace the AVG function by the combination SUM/COUNT.
For example, the following query:

SELECT student_name, AVG(test_score)
FROM student
GROUP BY student_name;

can be replaced by the one below:

SELECT student_name, SUM(test_score)/COUNT(test_score)
FROM student
GROUP BY student_name;

, , , ,

  1. #1 by Jim on 04 Oct 2011 - 18:12

    We ran into that as well. It’s not happening on our AWS instance though, but is on our database server. The way we coped with it was to make the GROUP BY and ORDER BY the same. I like your solution better.

  2. #2 by smoreau on 04 Oct 2011 - 18:27

    Thank you Jim.

  3. #3 by Shad on 02 Dec 2011 - 13:32

    I get this bug in v5.0.7, but it’s ok in v5.1.41 and v5.1.56!

    Thank you for posting that workaround!

(will not be published)