Darla Posted February 14, 2007 Share Posted February 14, 2007 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 14, 2007 Share Posted February 14, 2007 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 Quote Link to comment Share on other sites More sharing options...
camdagr81 Posted February 14, 2007 Share Posted February 14, 2007 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"; ?> Quote Link to comment Share on other sites More sharing options...
Darla Posted February 14, 2007 Author Share Posted February 14, 2007 Thanks both of you, just what I needed Quote Link to comment Share on other sites More sharing options...
Darla Posted February 14, 2007 Author Share Posted February 14, 2007 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? Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 14, 2007 Share Posted February 14, 2007 Are you gtting any errors? How are you trying to reference the value in the record set? Quote Link to comment Share on other sites More sharing options...
redarrow Posted February 14, 2007 Share Posted February 14, 2007 $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. Quote Link to comment Share on other sites More sharing options...
Darla Posted February 14, 2007 Author Share Posted February 14, 2007 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" Quote Link to comment Share on other sites More sharing options...
Darla Posted February 14, 2007 Author Share Posted February 14, 2007 Never mind, I figured it out. It was an problem with the reference for some reason, when using $row[0] it worked Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 14, 2007 Share Posted February 14, 2007 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'] Quote Link to comment Share on other sites More sharing options...
Darla Posted February 14, 2007 Author Share Posted February 14, 2007 Ah that worked nicely, thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.