mysql avg function with ROUND function

Question

In mysql query i have used

avg(column_name)  

which return value 12.50 but if i do round to avg function like

ROUND(avg(column_name))

then it returns value 12 but it need to be 13. I dont know why it is returning 12 instead of 13

If I do ROUND(12.50) it returns me value "13" and avg(column_name) which is 12.50. When I do ROUND(avg(column_name)) returns value "12". I think in both cases output needs to be "13".

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/precision-math-rounding.html:

As of MySQL 5.0.3, ROUND() uses the following rules depending on the type of the first argument:

For exact-value numbers, ROUND() uses the “round half up” or “round toward nearest” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the "round to nearest even" rule: A value with any fractional part is rounded to the nearest even integer.

The following example shows how rounding differs for exact and approximate values:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

Answer 2

try this:

ROUND(cast(avg(column_name) as decimal))

More Information Rounding Behavior

thanks for the reference 472084 and itsmeee