select average score

[materix]

Hi.

I have two tables:

Students:
studentID, name

Scores:
studentID, score

I need to do a query that displays all students together with their average scores. That is:
studentID, name, averageScore

How do I build this query?

Thx.

[Aaron D.]

Assuming you are using php and mysql this will do it.

$row = mysql_query("SELECT * FROM Students");
   if($first=mysql_fetch_array($row)){
do{
     $avg_score = mysql_fetch_array(mysql_query("SELECT AVG(score) as score FROM `scores` WHERE studentID = '$first[id]' ")); 
echo $first[id]' '.$first[name].' '.$avg_score[score].'<br/>';
}while($first=mysql_fetch_array($row));
} 

[swampBoogie]

Not quite the most efficient method. It can be done with one query

select s.studentID, s.name, avg(sc.score)
 from student s left join scores sc on s.studentid = sc.studentid
group by s.studentID, s.name

If you don't want students without score, remove the outer join.