a SELECT TOP question 1

[teach314]

hi

I have a table as shown below. The actual table would have about 150 records and about 10 different teams.

AthleteID    Team      Score
-----------------------------
   123       Bears      3.00
   124       Tigers     4.90    
   131       Bears      5.75
   132       Bears      7.20
   165       Tigers     9.00
   166       Tigers     9.00
   204       Tigers     1.75  
   222       Bears      9.80
   244       Tigers     5.50
etc...

I need to get the SUM of the TOP 3 scores for EACH team.
Based on the records above, the output would look like...

Team      Top3Sum
-----------------
Tigers     23.5           that's  9   + 9   + 5.5
Bears      22.75          that's  9.8 + 7.2 + 5.75

thanks for any help

 

[dhookom]

I would try something like:

SELECT Team, Sum(Score) as Top3Sum
FROM [Actual Table Name]
WHERE Score IN (SELECT TOP 3 Score FROM [Actual Table Name] T WHERE [Actual Table Name].Team = T.Team ORDER BY score Desc)
GROUP BY Team;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

thanks Duane. Works perfectly!

 

[teach314]

Hi Duane and all
The code you provided works perfectly in almost every case. Here's the one difficulty I'm having.

I've modified the original table to make the point...

AthleteID    Team      Score
-----------------------------
   123       Bears      3.00
   124       Tigers     9.00    
   131       Bears      6.00
   132       Bears      5.00
   165       Tigers     8.00
   166       Tigers     7.00
   204       Tigers     7.00  
   222       Bears      9.80
   244       Tigers     7.00
etc...

For Tigers, the SELECT TOP 3 returns 9.00, 8.00, 7.00, 7.00, 7.00, so the Top3Sum is too high. I just want 9.00, 8.00, and 7.00. I don't think using DISTINCT will help - if the top 3 scores were all 9.00, then we'd want to return all three of these. My problem is that I always want to return NO MORE THAN 3 scores.

Thanks in advance for any insights.
Teach314

 

[dhookom]

I think all you have to do is add some unique field to the ORDER BY clause:

SELECT Team, Sum(Score) as Top3Sum
FROM [Actual Table Name]
WHERE Score IN (SELECT TOP 3 Score FROM [Actual Table Name] T WHERE [Actual Table Name].Team = T.Team ORDER BY score Desc, AthleteID)
GROUP BY Team;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

hi Duane

Adding AthleteID to the INNER SELECT definitely restricts its output to 3 top values of score. But, in the OUTER SELECT, the WHERE can still 5 values of score that match these three.
Getting close!

teach314

 

[dhookom]

How about if you try:

SELECT Team, Sum(Score) as Top3Sum
FROM [Actual Table Name]
WHERE AthleteID IN (SELECT TOP 3 AthleteID FROM [Actual Table Name] T WHERE [Actual Table Name].Team = T.Team ORDER BY score Desc, AthleteID)
GROUP BY Team;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

hey Duane - that's perfect! I'm running a tournament in a few days and this will help a lot.
teach314

 

[dhookom]

We just need to assume an AthleteID has only one score.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[teach314]

good point. That is in fact true.