数据库聚合函数

数据库聚合函数

聚合函数 sum ,avg,max,min,count

1总分

SELECT SUM(student_result) FROM result;
SELECT student_no,SUM(studet_result) FROM
result WHERE student_no='6'

2平均分 avg

SELECT AVG(studet_result) FROM result;

3最大值 max 最小值 min

SELECT MAX(studet_result),MIN(studet_result) FROM result;

4计数count

SELECT COUNT(*) FROM student;
SELECT COUNT(*) FROM student WHERE grade_id=1;
SELECT COUNT(*) FROM student;

SELECT subject_id,
CASE
WHEN student_result<60 AND student_result>=0 THEN '不及格'
WHEN student_result>=60  AND student_result<=80 THEN'及格'
WHEN student_result>80  AND student_result<=100 THEN'优秀'
ELSE '作弊'
END AS '成绩等级'
FROM result

每个科目平均分

SELECT subject_id,AVG(student_result) FROM result GROUP BY subject_id