CODEWITHSUNDEEP
sqlt-sql
jimmyb
jimmyb

Reputation: 11

SQL (two Tables) Query most popular course

I am having trouble returning the most common course Question is: Write a Query to return the CourseName of the most common course

Tables

StudentCourses:
     StudentCourseID
     StudentID
     CourseID

Courses:
     CourseID
     CourseName

I have this

SELECT CourseName
From Courses c
WHERE CourseID = (SELECT Count(CourseID) AS Course_Value
                FROM StudentCourses
                Group BY CourseID
                Order BY Course_Value DESC
                LIMIT 1;

I know it isnt correct but am i correct using COUNT to find the most popular course correctly?

Upvotes: 1

Views: 347

Answers (2)

Brett
Brett

Reputation: 1550

SELECT Top 1 CourseName 
FROM   Courses
JOIN   StudentCourses
  ON   Courses.CourseId = StudentCourses.CourseId
GROUP BY CourseName
ORDER BY Count(CourseName) DESC

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269553

For this type of question, learn to use explicit join and top:

select top (1) c.courseid, c.coursename, count(*) as cnt
from studentcourses sc join
     courses c
     on sc.courseid = c.courseid
group by c.courseid, c.coursename
order by count(*) desc;

If you want ties, then use top with ties.

Upvotes: 3

Related Questions