Geek Answers Handbook

MYSQL Sum of calculation results

I am creating a query in mysql 5.0 to calculate the student semester. The source table (studentItemGrades) contains a list of assignments, etc., which will be used to calculate the final grade. Each assignment matters Possible Level, Class, and Weight. The calculation should group all similarly weighted elements and provide SUM (GRADE) / SUM (POSSIBLESCORE) based on the date range when the task was to be assigned. The problem I am facing is the final summation of all the individual weighted estimates. 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 request:

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

: SUM WeighedGrade? , , .

.

+5
2

, :

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
+8

, , select . , SQL-, .

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
+1

More articles:


All Articles