AVERAGE multiple rows in a column in Access

Question

I have the following tables:

Table 1
Student , Exam_ID
1      1      
2      1     
3      2     
1      2     
3      3     
2      3     
3      4     
1      4  

Table 2
Exam ID, Mark
(1   , 5)
(2 ,   4)
(3  ,  4)
(4 ,   5)

each exam is solved by pairs of students ... i want to be able to average the mark of all exams taken by each pair of student for example : Exams 2 and 4 are taken by the same pair of students (3,1) i want to be able to average the marks for those 2 exams which are(4,5)=4.5 and then rank those pairs from highest to lowest marks thank you

How can I include First_Name and Surname into the first table?

ypercubeᵀᴹ ypercubeᵀᴹ · Accepted Answer · 2011-05-02T22:32:14

SELECT
    a.Student AS studentA
  , b.Student AS studentB
  , AVG(T2.Mark) AS averageMark
FROM ( T2
      INNER JOIN T1 AS a
          ON a.Exam_ID = T2.Exam_ID
     )  
      INNER JOIN T1 AS b
          ON a.Exam_ID = b.Exam_ID
          AND a.Student < b.Student
GROUP BY a.Student
       , b.Student
ORDER BY AVG(T2.Mark) DESC

or this:

SELECT
    a.Student AS studentA
  , b.Student AS studentB
  , AVG(T2.Mark) AS averageMark
FROM ( T1 AS a
      INNER JOIN T1 AS b
          ON a.Exam_ID = b.Exam_ID
          AND a.Student < b.Student
     )
      INNER JOIN T2
          ON a.Exam_ID = T2.Exam_ID
GROUP BY a.Student
       , b.Student
ORDER BY AVG(T2.Mark) DESC

where it is more obvious how it works. The JOIN inside the parenthesis finds the couples and next JOIN relates the couples to the second Marks table.

AVERAGE multiple rows in a column in Access

1 Answers