mysql AVG limiting

[dreams4000]

Hey there,

Quick question for everyone. Is there any way when using the mysql AVG command to limit the length of the number given? I would like to keep the output looking like 3.75 or 3.50 etc.. Right now I sometimes get that, but other times I end up with something like 3.456345646456 and really that can get ugly when being displayed on a php site🙂 Any ideas? Thanks much!

[bastien]

use Round

from the mysql manual

ROUND(X,D)
Returns the argument X, rounded to the nearest integer. With two arguments, returns X rounded to D decimals.
mysql> SELECT ROUND(-1.23);
-> -1
mysql> SELECT ROUND(-1.58);
-> -2
mysql> SELECT ROUND(1.58);
-> 2
mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1
mysql> SELECT ROUND(23.298, -1);
-> 20
Note that the behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR() instead.

[dreams4000]

Very cool! Thanks much. Just for anyone else looking for something like this. Since I wasn't using a specific number as shown in the example above, but an average of a specific column, here is the statement I had:

SELECT ROUND(AVG(somecolumnname),2) FROM .......