CODEWITHSUNDEEP
mysqlsum
Max H
Max H

Reputation: 81

MYSQL Sum results of a calculation

I am building a query in mysql 5.0 to calculate a student semester grade. The initial table (studentItemGrades) contains the list of assignments etc which will be used to calculate the final grade. Each assignment has a PossibleScore, Grade and Weight. The calculation should group all similarly weighted items, and provide the SUM(GRADE)/SUM(POSSIBLESCORE) based on a date range of when the assignment was due. The problem I am encountering is the final summation of all the individual weighted grades. For example, the results currently produce the following:

CourseScheduleID    sDBID   AssignedDate    DueDate     Weight  WeightedGrade
1           519     2010-08-26  2010-08-30  10  0.0783333333333333
1           519     2010-09-01  2010-09-03  20  0.176
1           519     2010-09-01  2010-09-10  70  0.574

from the query:

SELECT CourseScheduleID, sDBID, AssignedDate, DueDate, Weight, 
((SUM(Grade)/SUM(PossibleScore))*(Weight/100)) AS WeightedGrade 
FROM studentItemGrades 
WHERE DueDate>='2010-08-23' 
AND DueDate<='2010-09-10' 
AND CourseScheduleID=1 
AND sDBID=519 
AND Status>0 
GROUP BY Weight

The question: How do I now SUM the three results in the WeighedGrade output? And by the way, this is part of a much larger query for calculating all grades for all courses on a particular campus.

Thanks in advance for your help.

Upvotes: 8

Views: 11425

Answers (2)

rxmalott
rxmalott

Reputation: 11

In order to sum the three results, you would need to requery the results of this select using another select with a group by. This could be done using a single sql statement by using subqueries.

SELECT sq.CourseScheduleID, sq.sDBID, SUM(sq.WeightedGrade) as FinalGrade
FROM
(
  SELECT CourseScheduleID, sDBID, AssignedDate, DueDate, Weight, 
         ((SUM(Grade)/SUM (PossibleScore))*(Weight/100)) AS WeightedGrade 
    FROM studentItemGrades WHERE DueDate>='2010-08-23' AND DueDate<='2010-09-10' 
         AND CourseScheduleID=1 AND sDBID=519 AND Status>0 GROUP BY Weight
) AS sq
GROUP BY sq.CourseScheduleID, sq.sDBID

Upvotes: 1

jisaacstone
jisaacstone

Reputation: 4284

You can use a subquery, like so:

SELECT SUM(WeightedGrade) FROM
(
  SELECT CourseScheduleID, sDBID, AssignedDate, DueDate, Weight, 
    ((SUM(Grade)/SUM(PossibleScore))*(Weight/100)) AS WeightedGrade 
  FROM studentItemGrades 
  WHERE DueDate>='2010-08-23' 
  AND DueDate<='2010-09-10' 
  AND CourseScheduleID=1 
  AND sDBID=519 
  AND Status>0 
  GROUP BY Weight
) t1

Upvotes: 14

Related Questions