get max count joing 2 tables

by john murphy
(new york)

I have 2 tables 1 STUDENT (ID, NAME , COURSE_ID)

2 COURSE(ID, NAME)
I need to get the COURSE.NAME and Count of students for the course that has the most students assigned to it in 1 select statement using Oracle 11g.

Comments for get max count joing 2 tables

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 24, 2015
Rating
starstarstarstarstar
Thanks
by: John

Thanks for the info I will as you say tweak it for oracle

Feb 23, 2015
Rating
starstarstarstarstar
You need a subquery
by: John

You're really asking two questions at the same time:
(1) which course has the most students?
(2) what is the name of the course found in q1
(3) how many students are on this course?

Howver once you start to consider the problem you realise that to answer question 1 you need to know how many students are on each course then you can sort them in descending order to find out which course has the most students on it.

in terms of SQL statements then it becomes:

SELECT course_id, count(student_id) num_students FROM students WHERE course ORDER BY num_students DESC

We've given the expression "count(student_id)" a column alias just to make the code more readable.

That gives us the id of the course with the most studnets on it at the top of the list. Now we can turn that into a subquery and just select the first row returned like this:

SELECT course_id, num_students FROM (
SELECT course_id, count(student_id) num_students FROM students WHERE course ORDER BY num_students DESC
) WHERE rownum=1

Now all we need to d is to translate the course_id into the course_name by joining the result of this subquery with the course table.

SELECT course_name, num_students FROM (
SELECT course_id, count(student_id) num_students FROM student WHERE course ORDER BY num_students DESC)
JOIN course USING (course_id)
WHERE rownum=1

The above SQL statement uses the ANSI join syntax rather than the Oracle join syntax but the result is the same.

You could of course dispense with teh sub query if you can tolerate having a list of courses in descending order of the number of students on each course. That would fulfil the problem description as well.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.