CODEWITHSUNDEEP
sqlpostgresql
Frode
Frode

Reputation: 65

Query-ing data to get average grade

I am trying to get the average grade by querying data from different tables and while it seems like everything is as it is supposed to be, the query does not work. Tables are the following: 

grade(id_grade, id_subject, student_code and grade)
student(student_code, first_name, last_name, birthdate, average_grade)
subject(id_subject, credits, subject_code, name, teacher)

Average grade is calculated using the following formula: (sum of all matching grades (that correspond to student code in student) * average grade from student table + credits from subject * newest added grade) / (sum of all matching grades + credits from the subject).

The query is the following:

UPDATE student AS s
SET 
    average_grade = ( 
        (SUM(g.grade) * s.average_grade + sub.credits * g.grade) / 
        (SUM(g.grade) + sub.credits) )
FROM grade AS g, subject AS sub
WHERE s.student_code = g.student_code AND 
    g.id_subject = sub.id_subject AND s.student_code = 141837;

Keep in mind that this is PostgreSQL so some input may differ from other SQL-s, but this is what I was able to come up with. I also tried the following as well, but also yielded no results:

UPDATE student AS s 
LEFT JOIN grade AS g ON (s.student_code = g.student_code) 
LEFT JOIN subject AS sub ON (g.id_subject = sub.id_subject) 
SET average_grade = (
    (SUM(g.grade) * s.average_grade + sub.credits * g.grade) / 
    (SUM(g.grade) + sub.credits)) 
WHERE s.student_code = 141837;

Any help is highly appreciated!

Upvotes: 0

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need to aggregate as a subquery. I don't think the average calculation is correct; I think you want something more like this:

UPDATE student s
    SET average_grade = g.average_grade
FROM (SELECT g.student_code,
             SUM(g.grade * s.credits) / SUM(s.credits) as average_grade
      FROM grade g JOIN
           subject s
           ON g.id_subject = s.id_subject
      GROUP BY g.student_code
     ) g
WHERE s.student_code = g.student_code AND
      s.student_code = 141837;

Upvotes: 1

Related Questions