Newbie Query question

[Guest]

I'm fairly new to this, so I could really use a hand.
My query has just 4 columns; StudID, SchoolID, SchoolName, ContestScore.

I need to find the sum of the top 3 scores from each school, then rank these
in descending order. (for schools with < 3 participants, I sum all of their
ContestScores) For example, the data shown below...

100 AD AdamsHigh 75
101 WC WalkerColleg 82
104 AD AdamsHigh 50
106 AD AdamsHigh 75
107 SH SmithSec 100
110 AD AdamsHigh 100
115 WC WalkerColleg 30
119 WC WalkerColleg 70
120 WC WalkerColleg 90

should produce the result...

AdamsHigh 250
WalkerColleg 242
SmithSec 100

 

[Allen Browne]

Use a subquery to get the top 3 scores for each school.

Details and example in:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

You can then build a Totals query based on that one to sum the results.

 

[Guest]

Allen - thanks for the great reference.

Use a subquery to get the top 3 scores for each school.

Details and example in:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

You can then build a Totals query based on that one to sum the results.

 

[John Spencer]

Pardon me, but that could cause problems if there are ties for the third
position. For instance, if the scores for
Adams High (4 records in sample) were all 100, the sum would end up being
400 for the "Top 3" and not 300.

That may or may not be the desired outcome. If the desired outcome is 300
then there is a solution that involves returning different columns in the
subquery.

Post back if you need further details.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Allen Browne]

John, did you have a suggestion for the article in the web page?

Under "Points to note", it says:
Adding the primary key field to the ORDER BY clause
differentiates between tied values.

Does it need more than that?

 

[Guest]

Thanks to both John & Allen for responding. Yes, I would like more detail if
that is possible. I'm VERY new at this, so please don't be shy about
providing detail. Thanks so much.

 

[John Spencer]

No, I meant to call the posters attention to the fact that she could be
getting ties returned. I was't sure she had a primary key value in here
table. I guess StudId could be a primary key.

Instead of something like

SELECT T1.SchoolID, SchoolName, Sum(ContestScore) as Total
FROM TableName as T1
WHERE T1.StudID in
(SELECT Top 3 T2.StudID
FROM TableName As T2
WHERE T2.SchoolId = T1.SchoolID
ORDER BY ContestScore Desc, StudID)
GROUP BY SchoolId, SchoolName
ORDER BY Sum(ContestScore)

I was afraid the poster might do

SELECT T1.SchoolID, SchoolName, Sum(ContestScore) as Total
FROM TableName as T1
WHERE T1.ContestScore in
(SELECT Top 3 T2.ContestScore
FROM TableName As T2
WHERE T2.SchoolId = T1.SchoolID
ORDER BY ContestScore Desc, StudID)
GROUP BY SchoolId, SchoolName
ORDER BY Sum(ContestScore)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

John & Allen: Yes, that's it exactly, aside from adding a DESC to the last
ORDER BY. I appreciate the spelled-out examples in this forum. For learners
like me, being able to pick through actual examples is definitely the way to
go!