mysql - 如何从两个表中计算 GPA > 3.5 的百分比？

Question

我有两个表 tblStudent 和 tblTranscript。我想计算 GPA > 3.5 的学生百分比。我创建了两个查询，分别给出 GPA 学生总数和 GPA > 3.5 学生总数。

我不知道如何将这两个表相除并将它们乘以 100 以获得 GPA > 3.5 的学生百分比。

我的查询是:

平均绩点 > 3.5

SELECT COUNT (GPA) FROM (
SELECT avg(Grade) as GPA, studentID from tblTranscript
GROUP BY studentID HAVING avg(Grade) > 3.5)

TotalCountWithGPA

SELECT COUNT (GPA) FROM (
SELECT avg(Grade) as GPA, StudentID FROM tblTranscript
GROUP BY StudentID)

解决方案:

SELECT

(COUNT (GPA) 
/ 
(SELECT COUNT(GPA) FROM (SELECT avg(Grade) as GPA, StudentID FROM tblTranscript GROUP BY StudentID)) * 100) as PercentageOfStudents

FROM (SELECT avg(Grade) as GPA, studentID from tblTranscript GROUP BY studentID HAVING avg(Grade) > 3.5)

Answer 1

试试这个，

SELECT 
(COUNT (studentID)/(SELECT COUNT(studentID) FROM tblTranscript)) as percentageOfStudents 
FROM (
        SELECT avg(Grade) as GPA, studentID from tblTranscript
        GROUP BY studentID HAVING avg(Grade) > 3.5)

希望它能像你期望的那样工作