#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    2
    Rep Power
    0

    Question ORDER BY Average of values in subtable


    The situation is that I have two tables, one "T_Team" and one "T_Player". T_Team contains a team name and index value, and T_Player contains a foreign key representing the team index, a player name, and a score.

    What I'm trying to do is create a select statement that will retrieve all the team names and order them by the average score for that team. The following obviously won't work because the nested select has no reference to the higher level table:

    SELECT T.Name
    FROM T_Team T
    ORDER BY AVG(
    SELECT P.Score
    FROM T_Player P
    WHERE P.FK_TeamIndex = T.Index
    )

    How does one get around this though?

    K.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,374
    Rep Power
    391
    Code:
    SELECT T.Name, avg(score) ascore
    FROM T_Team T join T_Player P
    on P.FK_TeamIndex = T.Index
    group by T.name
    order by ascore desc
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Posts
    2
    Rep Power
    0
    Thanks, that did it.

IMN logo majestic logo threadwatch logo seochat tools logo