[Resolved] Help with AVG

arsenalfan14

I'm using:

SELECT AVG(rating) as average_rating

I want to limit the decimals returned to 2. So my number is 5.33

Also, I want to get the highest average rating.

Can I do:

SELECT AVG(rating) as average_rating

then another query

SELECT MAX(average_rating)

Thanks!

LordShryku

SELECT AVG(rating) as average_rating
FROM table

This will only return one number, so getting the highest number wouldn't be involved. However, if you're grouping by a column, you wouldn't need two queries. You can nest your group columns like so...

SELECT MAX(AVG(rating))
FROM table
GROUP BY column

To limit the decimals, you can use one if the php functions:
[man]number_format[/man] or [man]round[/man]

arsenalfan14

Thanks.

I want it to display the overall average.

And the higest average, different.

Each id is a different rating from a game. So, I want the first to find the overall rating average and the second to find the higest average by id.

Understand?

Did:

<?=round($rating_rs->Fields("rating_value"), 3)?>

But it's only giving me 2.5 or 3

drawmack

I thing you could do this:

SELECT MAX(average) AS max, AVG(average) AS average FROM table GROUP BY average

but I didn't actually try that so it may night work or might need a little tinkering with to work.

drawmack

For the rounding problem, print out a total of all the averages and the number of averages and do the math by hand maybe 2.5 is the exact number. If you want it to always print with padded zeros at the end then try using http://www.php.net/printf or http://www.php.net/sprintf

arsenalfan14

No other way to get the 0's at the end?

My numbers are in the .php like this:
<?=($avgrating)?>

can't get fprint to work.

arsenalfan14

Please? Someone? I've tried several things and can't get any of them to work. I want to reduce my number to 2 decimals each time. 2.00, 2.50. 255. Here's my code:

<?

  while ( ! $arse_squad_rs->EOF ) {

$rating_rs = $conn->Execute ( "SELECT AVG(rating_value) as rating_value FROM rating WHERE player_id=" . $arse_squad_rs->Fields("player_id") . " AND match_id=" . $_GET['match_id'] )
	or die ( $conn->ErrorMsg() );




?>
<tr><td><?=($rating_rs->Fields("rating_value"))?></td></tr>

LordShryku

What's wrong with number_format?

arsenalfan14

It's spitting out 5 decimals right now. I only want 2 decimals.

I check all out four options posted in this thread, they may work, but not the way I inserted them.

I did number_format(AVG(rating_value)

but didn't work.

arsenalfan14

Is that right? or not?

LordShryku

No, number_format is a php function, not a sql function. You use number format on the variable returned from the sql statement.

ie.

<tr><td><?=number_format($rating_rs->Fields("rating_value"),2)?></td></tr>