CODEWITHSUNDEEP
mysqlsqljoinsubqueryinner-join
J Darling
J Darling

Reputation: 5

SQL Join - Calculate weighted students grades based on grade type

I am having trouble calculating student grades based on a specific grade type.

I have the following tables

Students
----------------
stu_id
stu_fname
stu_lname

Grades
----------------
grade_id
grade_name
grade_type
grade_possible

StudentGrades
-----------------
stu_grade_id
grade_id
stu_id
grade_earned

GradeTypes
----------------
grade_type
grade_type_name
grade_weight

What I want to do is divide the sum of the earned grades for each grade type by the sum of the grade possible for each grade type. Then multiple the quotient by the grade weight for that grade type. I want my end result to be the calculated grades for each student in each grade type. Then I can add them together to get the students final grade but I have to get the first part first.

I am new to using sql and I don't know if I am trying to do to much in one sql query. This is what I have tried and what I got when I tried it. 

SELECT S.stu_fname, S.stu_lname, GT.grade_type_name, 
(sum(SG.grade_earned)/sum(G.grade_possible) * GT.grade_weight) AS Calculated Grade 
FROM Student S
INNER JOIN StudentGrade SG ON SG.stu_id = S.stu_id
INNER JOIN Grade G ON SG.grade_id = G.grade_id
INNER JOIN GradeType GT ON G.grade_type = GT.grade_type;

I got something like this 

James | Fort | HW/QUIZ | 28.2

I knew this was wrong right away because the weight for HW/QUIZ is 20%

When I broke up the query and tested just the sum(SG.grade/earned) part. I received the sum of all of the students grades. I want the query to calculate individual student's grades for an individual grade type at a time.

Any help is appreciated, I am stuck. I have researched sub queries and joins and need more help to narrow my search to get the answer.

This is my updated query after implementing GROUP BY. I used this query to ROUND my answer. I figured out that I need 4 decimals places to keep the integrity of the answer.

Select S.stu_fname, S.stu_lname, GT.grade_type_name,         
(ROUND((SUM(SG.grade_earned)/SUM(G.grade_possible)), 2) * ROUND((GT.grade_weight/100.0)
, 2) ) as CalculatedGrade 
FROM Student S
INNER JOIN StudentGrade SG on SG.stu_id = S.stu_id
INNER JOIN Grade G on SG.grade_id = G.grade_id 
INNER JOIN GradeType GT WHERE G.grade_type = GT.grade_type
GROUP BY S.stu_fname, S.stu_lname, GT.grade_type_name;

It is possible to add the students grades together to get just one record for each student?

Upvotes: 0

Views: 2121

Answers (1)

Ikan
Ikan

Reputation: 71

when you use SUM you should use group by, tried this query:

SELECT S.stu_fname, S.stu_lname, GT.grade_type_name, 
(sum(SG.grade_earned)/sum(G.grade_possible) * GT.grade_weight) AS Calculated Grade 
FROM Student S
    INNER JOIN StudentGrade SG ON SG.stu_id = S.stu_id
    INNER JOIN Grade G ON SG.grade_id = G.grade_id
    INNER JOIN GradeType GT ON G.grade_type = GT.grade_type
GROUP BY S.stu_fname, S.stu_lname, GT.grade_type_name;

Upvotes: 1

Related Questions