3

Help with aggregate SQL query

view full story
linux-howto

http://www.daniweb.com – Just when I thought I was comfortable with SQL queries, another one popped up that I can't get around with.... I'm ALMOST there but not quite. Here is the situation... I'm dealing with two tables PoolTeams and PlayerStats The PoolTeams table has two columns: PoolTeamID and TeamName The PlayerStats table has the following columns: PlayerID, PoolTeamID, Weeknumber, Points and Status The idea here is to create a query that will add up all points for all players playing for PoolTeam X during Week 10 of the competition. The players' status has to be TRUE. My Query looks like this: sSQL = "Select  PS.PoolTeamID, PT.TeamName, Sum(PS.Points) From " _         & " PlayerStats PS, PoolTeams PT Where  " _         & " PS.Weeknumber = 10 AND " _         & " PS.Status = 'True' AND " _         & " PS.PoolTeamID = PT.PoolTeamID " _         & " Group By PT.TeamName, PS.PoolTeamID " So far so good. The query displays team names and their points, however this table is sorted by team name and I need it to be sorted by the amount of total points from highest to lowest. Now, if I try to change the bottom line to : & " Group By PS.Points, PS.PoolTeamID " I get an error message 3122 saying "You tried to execute a query that does not include the specified expression "TeamName" as part of an aggregate function. I think I'm close but would like to have the output sorted accordig to points. Can someone point me in the right direction? Thanks!!! (General)