Duplicate showing – Group By Rollup SQL

June 26, 2022

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)

>Solution :

It lokks 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 is that
GROUP BY ROLLUP (tblENROLLMENT.CourseId, tblCOURSE.CourseTitle)
is the equivalent of
GROUP BY GROUPING SETS ((tblENROLLMENT.CourseId, tblCOURSE.CourseTitle), (tblENROLLMENT.CourseId), ())

Note also:

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.