MYSQL计算的总和结果

Question

我正在用MySQL5.0构建一个查询来计算学生的学期成绩。初始表(studentItemGrades)包含作业列表等，这些作业将用于计算期末成绩。每个作业都有PossibleScore、等级和权重。计算应该对所有类似权重的项目进行分组，并根据作业到期的日期范围提供总和(成绩)/SUM(POSSIBLESCORE)。我遇到的问题是所有个人加权分数的最终总和。例如，结果当前生成以下内容：

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

从查询中：

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

问题:现在如何在WeighedGrade输出中对这三个结果求和？顺便说一句，这是一个更大的查询的一部分，用于计算特定校园中所有课程的所有分数。

提前感谢您的帮助。

Answer 1

您可以使用子查询，如下所示：

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

Answer 2

为了对这三个结果求和，您需要使用带有group by的另一个select重新查询此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