Collecting data and getting avarage!?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Collecting data and getting avarage!?

Post 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?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

look up mysql's: AVG() and ORDER BY
kpraman
Forum Contributor
Posts: 172
Joined: Fri Oct 13, 2006 10:54 am

Post by kpraman »

Code: Select all

$query_avg="SELECT AVG(numbers) FROM tbl_average WHERE criteriaid=$citeriaId ORDER BY criteriaid DESC":
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What does mysql_error() tell you?

I'll bet it has something to do with your ORDER BY clause.
jmansa
Forum Commoner
Posts: 81
Joined: Wed Aug 23, 2006 4:00 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Why are you using subqueries here?
Post Reply