Bonifacey - 1 year ago 66
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

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);
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download