5

This is part 2 of a problem that was already answered by peterm on this board. Thanks again peterm!

So I have code that will return the top 3 test scores for a given student. My table looks like the following:

StudentID, Test ID, Score
1,1, 95
1, 2, 90
1, 3, 90
1, 4, 90
2, 1, 99
2, 2, 95
2, 3, 90
2, 4, 90

Thanks to peterm, I have the following code which will do this for me:

SELECT StudentID, TestID, TestScore
  FROM MyTable t
 WHERE TestID IN
(
  SELECT TOP 3 TestID 
    FROM MyTable
   WHERE StudentID = t.StudentID 
   ORDER BY TestScore DESC, TestID
)
 ORDER BY StudentID, TestScore DESC, TestID;

My new problem is now I need to add two new fields to the table for Subject and Year, so I need to find the top 3 scores for each Subject-Student-Year combination. Once I have the top 3 scores for each combination, I need to average them so that I will have one averaged score of the top 3 scores for each student-subject-year combination. Hopefully, I've explained this clearly enough without having to mock up another table.

Thanks in advance.

user3143315
  • 75
  • 1
  • 2
  • 5

1 Answers1

7

You can do something like this

SELECT StudentID, Year, Subject,  AVG(TestScore) AS AvgScore
  FROM
(
  SELECT StudentID, Year, Subject, TestScore
   FROM MyTable t
   WHERE TestID IN
  (
   SELECT TOP 3 TestID 
     FROM MyTable
    WHERE StudentID = t.StudentID
      AND Year = t.Year
      AND Subject = t.Subject
    ORDER BY TestScore DESC, TestID
  )
) q
 GROUP BY StudentID, Year, Subject
 ORDER BY StudentID, Year, Subject;

Sample output:

| STUDENTID | YEAR | SUBJECT | AVGSCORE |
|-----------|------|---------|----------|
|         1 | 2012 |       1 |       91 |
|         1 | 2012 |       2 |       84 |
|         2 | 2012 |       1 |       94 |
|         2 | 2012 |       3 |       95 |

Here is SQLFiddle demo.
Demo as usually is for SQL Server but expected to work in MS Access, maybe with minor syntactic tweaks

peterm
  • 91,357
  • 15
  • 148
  • 157
  • What if the top scorer is 4 or more different StudentID having the same Year, Subject and TestScore? – Jade Jan 11 '14 at 06:49
  • Peterm, you are a god! Thankyou, thankyou, thankyou! Any tips on a good website to learn more about sql? I would like to figure out this logic when I have more time. – user3143315 Jan 11 '14 at 18:30
  • Jade - thank you for your post. What I'm looking for is to calculate the avg score of the top 3 tests for each student for each subject and year. So, a student may take 10 tests in English in 2013 and another 10 in Geometry for the same year. I want an output of 2 records for that student - one for Geometry in 2013 and one for English in 2013. The first value would take the top 3 English test scores and average them. The 2nd would do the same for Geometry. – user3143315 Jan 11 '14 at 18:37