MySQL 使用聚合函数进行选择

Question

我有一个简单的查询:

SELECT 
      score,subject_name 
 FROM exams_scores,subjects 
WHERE (admission_no = 2771) AND(subjects.subject_code = exams_scores.subject_code)

这给了我:所有 5 条记录均符合预期。如果我这样做:

SELECT 
      score,
      subject_name,
      SUM (score) as TOTAL 
FROM exams_scores,subjects
WHERE (admission_no = 2771) AND (subjects.subject_code = exams_scores.subject_code)

我只得到第一个分数。如果想获得所有分数加上它们的总和，我应该使用什么。

样本数据

admission_no       subject     score
1000               Engligh      80
1000               Math         90
1000               Physics      70
2000               English      45
2000               Math         80
2000               Physics      90

我正在尝试获取特定录取号的所有分数加上总分所以我期望:

入场号:1000，

得分:80,90,70

总计:240

Answer 1

听起来您正在寻找可以使用联合来完成的汇总。

SELECT score,subject_name 
 FROM  exams_scores,subjects 
WHERE  (admission_no = 2771) AND(subjects.subject_code = exams_scores.subject_code)
UNION ALL
SELECT sum(score), 'Total'
 FROM  exams_scores
WHERE  (admission_no = 2771)