Help with aggregate SQL query

[navaidstech]

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!!!

[Simon Tite]

Not too sure which Sql engine you are using, but the following syntax should work with most of them:

sSQL = "Select  PS.PoolTeamID, PT.TeamName, Sum(PS.Points) as pts From " _
        & " PlayerStats PS, PoolTeams PT Where  " _
        & " PS.Weeknumber = 10 AND " _
        & " PS.Status = 'True' AND " _
        & " PS.PoolTeamID = PT.PoolTeamID " _
        & " Group By PS.PoolTeamID  " _
        & "Order By pts Desc "

Points to note are:

You want to sort on an aggregated field, sum(PS.Points), so you need to give it an alias, using the "sum(PS.Points) as pts" .. some versions of SQL differ in this syntax, the most common alternative is just use space instead of the word as.

You only need to group on the team id, presuming the team id is unique, and the team name is just a description.

There is never usually any need to group on an aggregated function such as SUM, unless you expect only a few unique discrete values for the sum.

[navaidstech]

Hi Simon... thank you for the reply. I tried your code but it still seems to be not too happy. Still gives me the 3122 Error.

Here are a few variations that I have tried:

...
& " Group By PS.PoolTeamID  " _       
 & "Order By TotalPoints Desc "

Error 3122 saying that it does not include the TeamName expression as part of an aggregate function. So I changed it to:

...
& " Group By PT.TeamName   " _       
 & "Order By TotalPoints Desc "

Same Error but this time it complains about PoolTeamID not being included. Then I tried:

...
& " Group By PT.TeamName, PS.PoolTeamID   " _       
 & "Order By TotalPoints Desc "

This gave me Error 3061 - Too few parameters. Expected 1.
On I went to:

...
& " Group By PT.TeamName, PS.PoolTeamID   "

OK, no errors this time but the points are not sorted. So now I just went trial and error:

...
& " Group By PT.TeamName, PS.PoolTeamID, PS.Points   " _
& " Order By PS.Points Desc"

Works but this gives me a list of all players and their points. If I try to change PS.Points to Sum(PS.Points) then it complains about the fact that you can't use SUM in the GROUP BY clause.

Now, I'm not sure how to answer your question about the SQL engine. I'm using VB6 with Access97 database if that helps.

Also when giving the sum(PS.Points) an alias, I had to put AS because the query would not recognize a space.

Thanks
Alex

[Simon Tite]

Now, I'm not sure how to answer your question about the SQL engine. I'm using VB6 with Access97 database if that helps.

That's the answer!

Now it's quite a few years since I used the Access database, but I seem to remember that perhaps it doesn't like doing ORDER BY after GROUP BY. The specification of the SQL language used to be simply: SELECT ... FROM ... ORDER BY ... GROUP BY ... HAVING ... - and this means that you will have to use a subquery to get the results you want.

Firstly, maybe you should get the VB6 crap out of the way and just type your queries direct into the Access application. Anyway, that's up to you, but I'll just give you my suggestions as straight SQL statements to save typing all those quotes and underscord!

If you can get to a working query which outputs the correct results, but not sorted by the total points, this is a good starting point. I guess it should look something like this:

SELECT pt.PoolTeamID AS teamid, pt.TeamName AS teamname, Sum(ps.Points) as pts
FROM PlayerStats ps, PoolTeams pt
WHERE ps.Weeknumber = 10
AND ps.Status = 'True'
AND ps.PoolTeamID = pt.PoolTeamID
ORDER BY pt.PoolTeamId, pt.TeamName
GROUP BY pt.PoolTeamId, pt.TeamName

This should give you one line per teamid, with the correct total points for that team. The ORDER BY is required here, because the GROUP BY subtotalling works on change of pt.PoolTeamId, and after doing the join, there is no guarantee of any particular order in the result set.

Once this query is working, you can select from it, just as if it was an ordinary table. Now here, I'm not too sure of the Access syntax, but the most common way is just to surround the whole query with brackets, give it an alias, and then just use it wherever you would use a table. Thus:-

SELECT subq.teamid, subq.teamname, subq.pts
FROM (The query above[/i/]) subq
ORDER BY subq.pts DESC

Good luck!

[navaidstech]

Simon, thank you for your reply. Let me digest it a bit and I'll get back to you...

thanks!

Alex

[navaidstech]

Simon....

thank you, thank you, thank you.... subqquery was the way to go in this case. After playing with it a bit, I was finally able to extract the teams and points, and have them sorted in the proper order.

I'm glad I found this discussion group. I'm sure it will come handy in the future..

thanks again!

Alex

[Simon Tite]

Glad it worked for you, alex. It would be nice for me (and for others) if you could flag this thread as "solved". (And maybe give me a plus point for my answer?).

Hope your project goes well,

Simon

[navaidstech]

Glad it worked for you, alex. It would be nice for me (and for others) if you could flag this thread as "solved". (And maybe give me a plus point for my answer?).

Hope your project goes well,

Simon

No problem Simon, here it comes.
Thanks again for your help!
Alex