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?