[SOLVED] help finding average value

[cs1h]

Hi,

 

I have a problem that I can't work out, I'm trying to get a set of values from a database and then find the average of the values and round the number up to the nearest whole number.

 

The numbers that need to be worked out are all in the same column in the database and what I want to do is work out the average of all the rows.

 

Can any show me an example of how to do this?

 

Any help will be appreciated,

 

Colin

[littledragon]

Do you need mysql to do the averaging sum, or can you pull the query and do the work afterwards?

 

http://forums.devarticles.com/php-development-48/calculate-average-in-php-6934.html

 

mysql> describe math;

+-------+---------------------------+------+-----+---------+----------------+

| Field | Type                      | Null | Key | Default | Extra          |

+-------+---------------------------+------+-----+---------+----------------+

| id    | int(10) unsigned zerofill |      | PRI | NULL    | auto_increment |

| num  | int(10)                  | YES  |    | NULL    |                |

+-------+---------------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

 

mysql> select * from math;

+------------+------+

| id        | num  |

+------------+------+

| 0000000001 |    5 |

| 0000000002 |    5 |

| 0000000003 |    5 |

| 0000000004 |    5 |

| 0000000005 |    5 |

| 0000000006 |    7 |

| 0000000007 |    7 |

| 0000000008 |    7 |

| 0000000009 |    7 |

| 0000000010 |    7 |

+------------+------+

10 rows in set (0.00 sec)

 

mysql> select count(*) as cnt, SUM(num) as sum, (SUM(num)/count(*)) as avg FROM math;

+-----+------+------+

| cnt | sum  | avg  |

+-----+------+------+

|  10 |  60 | 6.00 |

+-----+------+------+

1 row in set (0.00 sec)

[phpzone]

Something like...

 

<?php

// connect to db first!

$sql = "SELECT AVG(totals) FROM products";

$result = mysql_query( $sql );

if ( $result )
{
    $average = round( mysql_result( $result, 0 ), 2 );

    echo sprintf("%.2f", $average);
}
else
{

  exit( "average lookup query error: " . mysql_error() );

}
?>