[Resolved] mySql avg() and limiting decimal places

[commandonz]

Hi all,

I'm running the following query succesfully to get back average scores for reviews in my database (headphone reviews). The values come back as "1.234": how would I limit this to one number after the decimal place? Do I have to iterate over the array in php and replace the value - if so, can anyone supply a bit of example code? I'm still not fully up to speed on php arrays.

Many thanks

Commando

function getReviewScores($headphoneId) {
   $query = "select headphoneId, count(bassExtension) as count,avg(midsQuality) as midsQuality,
 avg(comfort) as comfort FROM review WHERE headphoneId = {$headphoneId}
GROUP BY headphoneId ORDER BY date";

   $result = mysql_query($query, getDbConnection()) or die("getReviewScores select Failed!");

   xdebug_break();

   // Turn the results into an array
   $line = mysql_fetch_array($result, MYSQL_ASSOC);

   mysql_free_result($result);

   // Return the array to the caller to display
   return $line;
}

[Norman_Graham]

Hi Commando

I think you want to use MySQL commands to ensure that the result set you get is already in the form desired. If you look at the following manual page, you'll see two functions which you might find helpful: ROUND(X,D) and TRUNCATE(X,D). In both cases, you could use these functions to ensure that the results you get from your table are already either rounded or truncated to one decimal place.

Alternatively, you could set the data type in the appropriate column to only one decimal place.

HTH

Norm

[commandonz]

Perfect! Thanks a lot Graham! 🙂

I chained the two commands together worked:

ROUND(avg(bassExtension), 1)

Since it's a calculated value I wouldn't have thought you could just define the column as one decimal place - it's averaging integers.