时间戳

首页 > 解决方案 > 对于每门“CptS”课程，找出课程不及格的学生百分比。假设及格分数为 2 或以上

sql - 对于每门“CptS”课程，找出课程不及格的学生百分比。假设及格分数为 2 或以上

问题描述

CREATE TABLE Course ( 
courseno   VARCHAR(7), 
credits    INTEGER NOT NULL, 
enroll_limit INTEGER, 
classroom   VARCHAR(10), 
PRIMARY KEY(courseNo), ); 

CREATE TABLE Student ( 
sID   CHAR(8),
sName   VARCHAR(30), 
major VARCHAR(10), 
trackcode   VARCHAR(10), 
PRIMARY KEY(sID), 
FOREIGN KEY (major,trackcode) REFERENCES Tracks(major,trackcode) );

CREATE TABLE Enroll ( 
courseno    VARCHAR(7), 
sID   CHAR(8), 
grade FLOAT NOT NULL, 
PRIMARY KEY (courseNo, sID), 
FOREIGN KEY (courseNo) REFERENCES Course(courseNo), 
FOREIGN KEY (sID) REFERENCES Student(sID) );

到目前为止，我已经能够创建两个单独的查询，一个计算失败的人数。另一个计算通过的人数。我无法将这些组合起来产生通过人数/失败人数。对于每门课程。

SELECT course.courseno, COUNT(*) FROM course inner join enroll on enroll.courseno = course.courseno
WHERE course.courseno LIKE 'CptS%' and enroll.grade < 2
GROUP BY course.courseno;

SELECT course.courseno, COUNT(*) FROM course inner join enroll on enroll.courseno = course.courseno
WHERE course.courseno LIKE 'CptS%' and enroll.grade > 2
GROUP BY course.courseno;

最终结果应该类似于

courseno  passrate
CptS451   100
CptS323   100
CptS423   66

标签： sqlgroup-bycount

解决方案

您可以为此做一个条件平均：

select 
    courseno,
    avg(case when grade > 2 then 100.0 else 0 end) passrate
from enroll
where courseno like 'CptS%'

推荐阅读