SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Feb 2004
    Posts
    291
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    select average score

    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.

  2. #2
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming you are using php and mysql this will do it.
    PHP Code:
    $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));


  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Not quite the most efficient method. It can be done with one query

    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •