MYSQL Sum results of a calculation

Question

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.

Answer 1

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