How to find Average of student's grades using the last grade he got at a subject in case he got more

Question

I have two tables, one for Grades and one for Students. The Grades table has a column Student which is a foreign key referencing to Student.Id.

Students have multiple grades at different subjects and some of the students have more than one grade at a subject. If a student has more than one grade at a subject, how can I do the general average of those grades using only the last grade he got at each subject, rather than all grades?

For example, if a student has Maths=3,History=5,Physics=4,History=7 I want to do the average using only Maths(3),Physics(4) and the last History(7), without the History(5).

As you can see in that image, i have a Students table with Name, Group, Location and a Grades table that have those grades. I want to do the average of those grades but in case there are more than 1 grad at the same subject the last grade at that subject should be used. In this case the avg should be AVG of French(9) + Maths(10) + the last grade taken at physics which is 5.

I'm sorry i just cant figure out what to do in this case. I am new to using relational databases, foreign keys etc.

So far i thought about using de Id to differentiate the grades. So for Physics 4 which is the first grade of that student it has the id of 1 and the Physics 5 which is the last grade he got at Physics it has the id of 7. So to get the last grade of that subject it should take the one with the higher id number but i can't figure out how to do it.

Answer 1

use the MAX() function to get the most recent grade for each subject. You can then calculate the average of these grades using the AVG() function.

Here is an example:

    SELECT Students.Name, AVG(Grades.Grade)
FROM Students
JOIN (
    SELECT Student, Subject, MAX(Date) AS LatestDate
    FROM Grades
    GROUP BY Student, Subject
) AS LatestGrades ON Students.Id = LatestGrades.Student
JOIN Grades ON LatestGrades.Student = Grades.Student
    AND LatestGrades.Subject = Grades.Subject
    AND LatestGrades.LatestDate = Grades.Date
GROUP BY Students.Name

Answer 2

Assuming a reasonable schema definition (please share the actual schema definition with column names and types in the question, and do not use an image to do it), you want something like this:

SELECT s.ID, s.Name, s.[Group], s.Location, AVG(g.Grade)
FROM 
( 
    SELECT Student, Subject, Grade
        ,row_number() over (partition by student, subject order by [ID] desc) rn
    FROM Grades
) g
INNER JOIN Students s ON s.Id = g.Student
WHERE rn = 1
GROUP BY s.ID, s.Name, s.[Group], s.Location

This uses a windowing function to partition and then number rows per student/subject based on the date, so you can then limit to only the first row within each partition and take the average from there. It should tend to perform better than solution which need to JOIN to a projection selecting by the MAX(date).

Note I said "reasonable schema" at the beginning of the answer. There is a concern based on the image (which again: it gets squished in the view and is nearly unreadable — don't post data that way!) that multiple grades for a subject are stored in the same column as comma-separated data. This is what we call an unreasonable schema. Storing comma-separated data in a single column is considered a BROKEN schema design: something that needs to be fixed.

Another important thing to understand is the use of the Date column, which was not shown in the question. SQL databases explicitly disclaim any responsibility for preserving any kind of natural order for your records. There is no such thing as insert order, table order, or disk order, and there are a number of things that can cause a database to reshuffle records on disk or return rows in a different order, even for consecutive runs of the same query.

Therefore, you MUST have a way to define "last grade" in terms of actual data in the rows. Even a sequentially assigned ID would be enough, but we need something. Otherwise, you will not be able to write this query! What is it that makes History(7) be last rather than History(5)?

Answer 3

I think you have to create a new table first that has the LatestGrades and then create the JOIN. Looks like You don’t even need the subj column in the final output, just an avg score across all subjects for every student.

WITH
Newtable AS 
(SELECT id, subject,grade, MAX(date) as LatestDate
FROM Grades
GROUP BY student, subject)
SELECT Students.id, students.name, AVG (newtable.grade) AS Avggrade
FROM (Newtable FULLJOIN Grades
ON Newtable.id=Grades.id) 
FULL JOIN Students ON students.id=grades.id