|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now! |
|
#1
April 19th, 2004, 12:50 PM
|
|||
|
|||
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
April 19th, 2004, 01:22 PM
|
|||
|
|||
|
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
|
|
#3
April 19th, 2004, 01:52 PM
|
|||
|
|||
|
Thanks, that did it.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > ORDER BY Average of values in subtable |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
