Bonifacey Bonifacey - 1 month ago 10
SQL Question

Retrieve highest two scores

To calculate the average score in an exam I need to get the results for:

subject1 + subject 2 + subject3 + (sum of best 2scores of subject 4, 5, 6) + (Best 2 scores in subject 7,8,9,10,11)

some of the subjects will have null as a student only takes any 3 out of 7,8,9,10,11

Each exam is defined by an ExamDateID

How do I achieve this? (I need the result to populate a new table... Not the scope of this question)

Access 2010

Main table

Answer

Consider using three source queries to be referenced in a final query. Do note, marks that tie in same ExamDateID and Subject range will be summed up in calculations:

  1. Exam Marks for Subjects 1-3 (using conditional aggregation)

    SELECT e.StudentID, e.ExamDateID, 
           SUM(IIF(e.Subject BETWEEN 1 AND 3, e.Marks, NULL)) AS SumMarks123
    FROM ExamTable AS e
    GROUP BY e.StudentID, e.ExamDateID;
    
  2. Exam Marks for Subjects 4-6 (highest two) (using subquery)

    SELECT e.StudentID, e.ExamDateID, SUM(e.Marks) AS SumTop2Marks456
    FROM ExamTable AS e
    WHERE e.Subject BETWEEN 4 AND 6 
    AND  (SELECT Count(*) FROM ExamTable sub
          WHERE sub.StudentID = e.StudentID AND sub.ExamDateID = e.ExamDateID
          AND sub.Subject BETWEEN 4 AND 6 AND sub.Marks >= e.Marks)  <= 2
    GROUP BY e.StudentID, e.ExamDateID;
    
  3. Exam Marks for Subjects 7-11 (highest two) (using subquery)

    SELECT e.StudentID, e.ExamDateID, SUM(e.Marks) AS SumTop2Marks711
    FROM ExamTable AS e
    WHERE e.Subject BETWEEN 7 AND 11
    AND  (SELECT Count(*) FROM ExamTable sub
          WHERE sub.StudentID = e.StudentID AND sub.ExamDateID = e.ExamDateID
          AND sub.Subject BETWEEN 7 AND 11 AND sub.Marks >= e.Marks)  <= 2
    GROUP BY e.StudentID, e.ExamDateID;
    

Final Query

SELECT a.StudentID, a.ExamDateID, 
       (a.SumMarks123 + b.SumTop2Marks456 + c.SumTop2Marks711) As SumScore
FROM (ExamAvgSubj123Q a 
INNER JOIN ExamAvgSubj456Q b 
    ON (a.ExamDateID = b.ExamDateID) AND (a.StudentID = b.StudentID)) 
INNER JOIN ExamAvgSubj711Q c 
    ON (a.ExamDateID = c.ExamDateID) AND (a.StudentID = c.StudentID);

Of course there is no reason, you cannot combine all queries into one but as seen can be a bit intense to maintain using derived tables:

SELECT a.StudentID, a.ExamDateID, 
       (a.SumMarks123 + b.SumTop2Marks456 + c.SumTop2Marks711) As SumScore    
FROM
   ((SELECT e.StudentID, e.ExamDateID, 
            SUM(IIF(e.Subject BETWEEN 1 AND 3, e.Marks, NULL)) AS SumMarks123
    FROM ExamTable AS e
    GROUP BY e.StudentID, e.ExamDateID) a 

INNER JOIN 
  (SELECT e.StudentID, e.ExamDateID, SUM(e.Marks) AS SumTop2Marks456
   FROM ExamTable AS e
   WHERE e.Subject BETWEEN 4 AND 6 
   AND  (SELECT Count(*) FROM ExamTable sub
         WHERE sub.StudentID = e.StudentID AND sub.ExamDateID = e.ExamDateID
         AND sub.Subject BETWEEN 4 AND 6 AND sub.Marks >= e.Marks)  <= 2
   GROUP BY e.StudentID, e.ExamDateID) b 

ON (a.ExamDateID = b.ExamDateID) AND (a.StudentID = b.StudentID)) 

INNER JOIN 
  (SELECT e.StudentID, e.ExamDateID, SUM(e.Marks) AS SumTop2Marks711
   FROM ExamTable AS e
   WHERE e.Subject BETWEEN 7 AND 11
   AND  (SELECT Count(*) FROM ExamTable sub
         WHERE sub.StudentID = e.StudentID AND sub.ExamDateID = e.ExamDateID
         AND sub.Subject BETWEEN 7 AND 11 AND sub.Marks >= e.Marks)  <= 2
   GROUP BY e.StudentID, e.ExamDateID) c 

ON (a.ExamDateID = c.ExamDateID) AND (a.StudentID = c.StudentID);