[SOLVED] mysql: fetch average from multiple rows (both numeric and text)

[Darla]

Hello

 

Just wondering the best way to fetch average values from a table. I wish to fetch both the average age and the most commonly used name from a table which has the fields

 

age          name

21            michelle                                       

15            sean

22            lisa

65            michelle

43            stan

 

etc.

 

Anyone have an idea what is the simplest and best way to do this? I guess I can use the AVG-function for the age, but how about the names? (From the above example the name michelle should stand out as the result)

 

Darla

[Psycho]

I *think* you would need to use two queries. There may be a way to combine the average with the most common name (which is not an average it is a ceiling). But, you do not state what you would want to happen if there are multiple names that are used with the same frequency. If there are 3 names that are used 10 times each, you can certainly do that, with some added coding on the PHP side.

 

Here is a query that will get the name used the most times (or one of the names used the most times if there are more than one).

 

SELECT name, count(name) as count FROM table GROUP BY name ORDER BY count DESC LIMIT 1

 

 

[camdagr81]

This may not be the MOST efficient example but it should work well

<?php
$mnm = '';
$cnt = 1;
$tot = 0; 
$names = array();

$sql = "SELECT `name`, `age` FROM `db` ORDER BY `name` ASC";
$dbq = mysql_query($sql);
$num = mysql_num_rows($dbq);

while (list($name, $age) = mysql_fetch_row($dbq)) {
	$tot += $age;
	if ($mnm != $name) { 
		$names[$name] = "$name: $cnt";
		$mnm = $name; 
		$cnt = 1;	
	}
	else { $cnt++; }
}

$avg = round($tot/$num);
foreach($names as $getUsed) {
	echo $getUsed . '<br>';
}
echo "<br>sAverage age: $avg";
?>

[Darla]

Thanks both of you, just what I needed

[Darla]

I have an issue with the AVG query - it works directly in mysql, but not from php (I tried with another query which worked from php so it is not the surrounding code). Do I have to escape something perhaps?

 

$result = mysql_query("SELECT AVG(age) FROM persons");

Any ideas?

[Psycho]

Are you gtting any errors? How are you trying to reference the value in the record set?

[redarrow]

$result = mysql_query("SELECT AVG(`age`) FROM `persons`");
$query=mysql_query($result);

 

 

try this but make sure the query name's are all diffrent ok.

[Darla]

Hello

 

Did not seem to help either, I get no error messages. Here is the code:

$result = mysql_query("SELECT AVG(age) FROM persons");
if (!$result) {
   die('Invalid query: ' . mysql_error());
}

print_r ($result);

while($row = mysql_fetch_array($result))
  {
  echo $row['age'];
  }

 

I tried the print_r just to see what happened and I got the output "Resource id #3"

[Darla]

Never mind, I figured it out. It was an problem with the reference for some reason, when using $row[0] it worked

[Psycho]

Never mind, I figured it out. It was an problem with the reference for some reason, when using $row[0] it worked

 

That's why I asked how you were referencing the value. Generating an average of a column will not give that value the name of the column.

 

If you prefer referencing your values by name (which I do since it makes debugging/modifying the cope much easier) you cuold do this:

 

$result = mysql_query("SELECT AVG(age) as avgAge FROM persons");

 

You could then reference the value using $row['avgAge']

[Darla]

Ah that worked nicely, thanks