mysql - 无法让 SUM 在 mysql 中工作

Question

我需要这个

  GROUP_ID     TOTALSUM       
     1          67,40000000
     2          56,5454

通过此查询

 select 
     exam.id, exam.ponderation / totalponderation *  avg(scores.result) as totalsum
 from 
    scores, exam,
    (SELECT SUM(ponderation) AS totalponderation
     FROM exam) AS sumponderation
 where 
     exam.group_id in (91, 93) and exam.id = scores.exam_id
 group by 
     exam.name

我尝试了很多查询，但没有任何效果。

查询#1(不起作用)(查询错误)

select sum(exam.ponderation / totalponderation *  avg(scores.result)) as totalsum

查询 #2(不起作用)(它返回 251 作为总数)

 select sum( exam.ponderation / totalponderation *  avgscores )
 from scores, exam,
      (SELECT SUM(ponderation) AS totalponderation
       FROM exam) AS sumponderation,
      (SELECT avg(scores.result) AS avgscores
       FROM scores, exam
       where exam.group_id in(91,93)  and exam.id = scores.exam_id) AS avgponderation

那么...关于我可以对我的列进行求和吗？

编辑

我的考试表

id----name-----ponderation----group_id---subject_id----date-
1------test1----------150----------4 ------------4-------------2011-11-11
2------test2----------20----------4 ------------4-------------2011-11-11
3-------test3---------20---------3--------------4-------------2011-11-11

我的分数表

id----exam_id-----user_id----subject_id result-------------date------order
1------1------------5-------------4 ------------80-------------2011-11-11-------1
2------2------------25-------------4 ------------30-------------2011-11-11------0
3------1------------5-------------4 ------------61-------------2011-11-11-------1
4------2------------25-------------4 ------------80-------------2011-11-11------0

编辑:我需要按 group_id 分组

  GROUP_ID     TOTALSUM       
         1          67,40000000
         2          56,5454

谢谢

Answer 1

也许在使用它之前计算另一个内联 View 中的 avgscores 可能会解决您的问题

SELECT 
e.group_id,
   SUM(e.ponderation /  totalponderation *  avgscores)  TOTAL_sum

FROM 
    exam e

    INNER JOIN (SELECT exam_id, avg(scores.result) avgscores 
         FROM scores
      GROUP BY exam_id) a
    ON e.id = a.exam_id,
    (SELECT SUM(ponderation) AS totalponderation
      FROM exam) AS sumponderation
Group by 
   e.group_id