Get the course which has maximum no.of students enrolled

• ralm

  Hall of Fame

  Points: 3156

  More actions

  April 1, 2012 at 1:11 pm

  #254064

  Hi ,

  We have 3 tables for ex student(sid,sname,dob), course(cid,cname,duration) and enroll(sid,cid,marks)

  I need a query to get the course which has maximum no.of students enrolled.

  --- sample table and data

  create table student(sid smallint identity,sname varchar(20) not null,dob smalldatetime)

  create table course(cid smallint identity,cname varchar(20) not null,duration int)

  create table enroll(sid int not null,cid int not null,marks int)

  Insert into student values ('XXX','1990/2/12'), ('AAA,'1990/2/12'), ('YYY','1990/2/12'), ('ZZZ','1990/2/12')

  insert into course values ('C1',3), ('C2',3), ('C2',3)

  insert into enroll values (1,1,null), (1,2,null), (2,1,null), (2,2,null), (2,3,null),(3,1,null),(4,2,null)

  I have a working solution using CTE. I want the result with simple select with group by and having.

  Might look like homework :hehe:

  ^{[font="Verdana"]Regards,
  Rals[/font].}

• okbangas

  SSChampion

  Points: 11773

  More actions

  April 1, 2012 at 1:18 pm

  #1467453

  Looks very much like homework, yes. So, what about give it at try yourself, and then post your attempt to solve it?

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  April 1, 2012 at 8:13 pm

  #1467474

  Should be a fairly simple join with a MAX(), but show us what you have.

• ralm

  Hall of Fame

  Points: 3156

  More actions

  April 1, 2012 at 11:19 pm

  #1467483

  I have got in CTE

  ;with getMaxCourse AS

  (Select cid,count(sid) over (partition by cid) Kount From enroll)

  Select cid from getMaxCourse where Kount = (Select max(Kount) from getMaxCourse)

  ^{[font="Verdana"]Regards,
  Rals[/font].}

• Loundy

  SSCertifiable

  Points: 6080

  More actions

  April 2, 2012 at 3:15 am

  #1467525

  could use something like this...?

  SELECT TOP 1 c.cid

  ,c.cname

  ,COUNT(*) AS [Number_Of_Students]

  FROM course c

  INNER JOIN enroll e

  ON c.cid = e.cid

  GROUP BY c.cid, c.cname

  ORDER BY Number_Of_Students DESC

  the problem with this is that its taking a random top record but in your sample data there are 2 courses with the same count of students.

  [font="Times New Roman"]There's no kill switch on awesome![/font]

• ralm

  Hall of Fame

  Points: 3156

  More actions

  April 2, 2012 at 3:29 am

  #1467528

  Thanks

  ^{[font="Verdana"]Regards,
  Rals[/font].}