Question

一张学生成绩表内SQL查询每个班级每门课程的平均分

已知：学生成绩表 A 有班级，学生号，课程，分数列
问：SQL查询每个班级每门课程的平均分，及格率

Answer 1

平均分：
select avg(分数) from a group by 班级,课程
及格率：
select (select count(*) from a where 分数>60 group by 班级,课程)
/(select count(*) from a group by 班级,课程)

Answer 2

---------------------------------------------------
-- sql script for SQL Server
--
---------------------------------------------------
SELECT
[班级],
[课程],
avg([分数]) as [平均分],
SUM(CASE WHEN [分数]>=60 THEN 1 ELSE 0 END)/COUNT(*) AS [及格率]
FROM [成绩]
GROUP BY [班级],[课程]

Answer 3

select avg(分数) as 平均分, rtrim(cast(sum(case when 分数>=60 then 1 end)/count(*)*100 as char(10)))+'%' as 及格率 from A group by 班级,课程