Page 1 of 1

Collecting data and getting avarage!?

Posted: Wed Apr 04, 2007 9:24 am
by jmansa
How do I collect data from a certain table depending on ID and get the avareage of all the data (numbers).
Also after collecting data from the ID's the compare the ID's and list them by ID with the highest avarege and desc?

Posted: Wed Apr 04, 2007 9:25 am
by Kieran Huggins
look up mysql's: AVG() and ORDER BY

Posted: Wed Apr 04, 2007 9:40 am
by kpraman

Code: Select all

$query_avg="SELECT AVG(numbers) FROM tbl_average WHERE criteriaid=$citeriaId ORDER BY criteriaid DESC":

Posted: Wed Apr 04, 2007 9:51 am
by jmansa
I tryid this but get an error on the fetch_array?

Code: Select all

$query = "SELECT Sum, Name FROM";
$query = $query & " (select AVG(list.Point) AS Sum, Name FROM list GROUP BY Name) ORDER BY Sum DESC";        
$result = @mysql_query ($query); // Run the query.
$row = mysql_fetch_array($result);

echo '<table width="100%" border="0" cellspacing="0" cellpadding="0"> 
  <tr>
    <td width="20" valign="top"><table width="20" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td><div align="left">1</div></td>
      </tr>
      <tr>
        <td><div align="left">2</div></td>
      </tr>
      <tr>
        <td><div align="left">3</div></td>
      </tr>
      <tr>
        <td><div align="left">4</div></td>
      </tr>
      <tr>
        <td><div align="left">5</div></td>
      </tr>
      <tr>
        <td><div align="left">6</div></td>
      </tr>
      <tr>
        <td><div align="left">7</div></td>
      </tr>
      <tr>
        <td><div align="left">8</div></td>
      </tr>
    </table></td>
    <td valign="top">';
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo '<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>'; 
	echo $row['Name'];
	echo '</td><td align="right">'; 
	echo $row[0];
	echo '</td></tr></table>'; 
} 
	echo '</td>
  </tr>
</table>';
Can somebody please help?

Posted: Wed Apr 04, 2007 9:53 am
by feyd
What does mysql_error() tell you?

I'll bet it has something to do with your ORDER BY clause.

Posted: Wed Apr 04, 2007 10:30 am
by jmansa
I inserted

Code: Select all

$result = mysql)query($query) or die (mysql_error());
instead of

Code: Select all

$result = @mysql_query ($query); // Run the query.
And got this error
Every derived table must have its own alias
Does that make any sense?

Posted: Wed Apr 04, 2007 10:37 am
by feyd
Why are you using subqueries here?