CREATE TABLE #Studentcourseschedule(
Department int,
Courseid int,
studentid int)
go
insert into #Studentcourseschedule(Department, Courseid,studentid)
values(1,1,1)
,(1,2,1)
,(1,3,1)
,(1,1,5)
,(1,3,6)
,(1,1,7)
,(2,2,1)
,(2,1,2)
,(2,3,2)
go
select department, COUNT(studentid) Studentcount
from #Studentcourseschedule
group by department
order by Department
go
select department, courseid, COUNT(studentid) Studentcount
from #Studentcourseschedule
group by department,courseid
order by Department, Courseid