GROUP BY ROLLUP统计选课人数出现重复结果的SQL问题

需求为按课程名称分组统计选课学生人数，同时通过ROLLUP生成所有课程的选课总人次统计行。
你当前编写的查询存在逻辑问题：GROUP BY ROLLUP (tblENROLLMENT.CourseId, tblCOURSE.CourseTitle) 传入了两个一一对应的字段（CourseId是课程表主键，和CourseTitle一一映射），会生成多余的中间聚合层级，返回冗余重复的统计行。

直接关联两张表后，按课程名称做ROLLUP分组即可，不需要同时传入CourseId和CourseTitle两个字段：

SELECT 
  tblCOURSE.CourseTitle, 
  COUNT(tblENROLLMENT.StudentID) AS 'Number Of Students' 
FROM tblENROLLMENT
INNER JOIN tblCOURSE ON tblENROLLMENT.CourseId = tblCOURSE.CourseId
GROUP BY ROLLUP (tblCOURSE.CourseTitle);

如果使用的数据库开启了严格分组模式（比如MySQL开启ONLY_FULL_GROUP_BY、旧版本SQL Server），要求SELECT中的非聚合字段必须全部出现在GROUP BY中，可以把CourseId也加入分组，但不要把两个字段都放进ROLLUP的参数里，避免生成无效聚合行：

SELECT 
  tblCOURSE.CourseTitle, 
  COUNT(tblENROLLMENT.StudentID) AS 'Number Of Students' 
FROM tblENROLLMENT
INNER JOIN tblCOURSE ON tblENROLLMENT.CourseId = tblCOURSE.CourseId
GROUP BY tblCOURSE.CourseId, tblCOURSE.CourseTitle WITH ROLLUP;

用你提供的测试数据运行上述语句，返回结果如下：

• Advanced Computer Programming：2人选课
• Fundamentals of Database Systems：2人选课
• Fundamentals of Programming：2人选课
• Introduction to Information Storage & Retrieval：2人选课
• Introduction to Information Systems and Society：2人选课
• 总计行（CourseTitle显示为NULL）：共10选课人次，和插入的10条选课记录完全匹配。

课程表插入语句 #

INSERT INTO tblCOURSE
(CourseId,CourseTitle,CourseCode,CrdtHrs) VALUES
(1,'Fundamentals of Programming','INSY2022',5),
(2,'Advanced Computer Programming','INSY2031',5),
(3,'Fundamentals of Database Systems','INSY2013',5),
(4,'Introduction to Information Systems and Society','INSY2033',4),
(5,'Introduction to Information Storage & Retrieval','INSY3093',4);

选课表插入语句 #

INSERT INTO tblENROLLMENT(EnrollId,CourseId,StudentID,DateofEnrollment,MidExResult,ProjectResult,FinalExResult)
VALUES
(1,1,1,'2020-01-01',20,21,50),
(2,2,2,'2020-01-01',20,27,50),
(3,3,3,'2020-01-01',20,22,50),
(4,4,4,'2020-01-01',20,20,50),
(5,5,5,'2020-01-01',20,17,50),
(6,1,6,'2020-01-01',20,10,50),
(7,2,1,'2020-01-01',20,29,50),
(8,3,1,'2020-01-01',20,28,50),
(9,4,5,'2020-01-01',20,25,50),
(10,5,1,'2020-01-01',20,50,50);

内容的提问来源于stack exchange，提问作者Moti