I'm trying to get the Count of Students Group by Course Name

Here's the query

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

Started from this query and was trying to get the CourseTitle from the tblCourse Table

SELECT Count(StudentID) AS 'Number Of Students' FROM tblENROLLMENT
INNER JOIN tblCOURSE on tblENROLLMENT.CourseId = tblCOURSE.CourseId
GROUP BY ROLLUP (tblENROLLMENT.CourseId) 

currently taking Fundamentals of Database Systems, any solutions?

Here are the tables

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)

CodePudding user response：

It looks like you need GROUPING SETS

SELECT
  CASE WHEN GROUPING(c.CourseTitle) = 1 THEN 'Total' ELSE c.CourseTitle END AS CourseTitle,
  COUNT(*) AS [Number Of Students]
FROM tblENROLLMENT e
INNER JOIN tblCOURSE c on e.CourseId = c.CourseId
GROUP BY GROUPING SETS (
    (e.CourseId, c.CourseTitle),
    ()
);

db<>fiddle

The problem was that
GROUP BY ROLLUP (tblENROLLMENT.CourseId, tblCOURSE.CourseTitle)
is the equivalent of
GROUP BY GROUPING SETS ((tblENROLLMENT.CourseId, tblCOURSE.CourseTitle), (tblENROLLMENT.CourseId), ())
so you end up with intermediate grouping rows also.

Note also:

• GROUPING() returns 1 for the Total row, so we use that to show the text Total. Otherwise you would get a NULL.
• Don't use '' to quote column names, use [] instead.
• Count(tblENROLLMENT.StudentID) is the same as COUNT(*) if StudentID is not null.
• Use short meaningful aliases to make your query more readable.
• Likewise for good formatting.