CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Sakshi Negi
Sakshi Negi

Reputation: 13

Write a query to display the name of the department that has the maximum student count

this is the schema Write a query to display the name of the department that has the maximum student count.

this is what is tried.

select d.department_name,count(s.student_id)
     from department d left join student s
     on d.department_id=s.department_id 
     group by d.department_name,d.department_id
     order by d.department_name;

and i think there is something missing in my code

Upvotes: 0

Views: 16824

Answers (4)

Yogesh
Yogesh

Reputation: 305

Select * from (
SELECT D.DEPARTMENT_NAME, COUNT(S.DEPARTMENT_ID) AS STAFF_COUNT 
FROM DEPARTMENT D, STAFF S
WHERE D.DEPARTMENT_ID = S.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
ORDER BY STAFF_COUNT DESC)
where rownum=1;

This query will give department name that has maximum number of student count

Upvotes: 0

Varshini
Varshini

Reputation: 11

I tried this and it worked.

select department_name 
from department d inner join student s 
on s.department_id=d.department_id 
having count(*) in (
    select max(count(student_id)) 
    from student s join department d 
    on d.department_id=s.department_id 
    group by d.department_id)
group by d.department_id,department_name;

Upvotes: 0

Harsh Bhatia
Harsh Bhatia

Reputation: 11

Based on the schema mentioned, you would have to make a join (INNER JOIN) to the department table from the staff table to get the name of the department.

If the name of the department is not desired and the counts can just be based on the department_id, then a join is not required.

The queries for both the scenarios is mentioned below.

Oracle SQL query for result with the department name, i.e. with INNER JOIN

SELECT D.DEPARTMENT_NAME, COUNT(S.DEPARTMENT_ID) AS STAFF_COUNT FROM **DEPARTMENT D, STAFF S** --INDICATES INNER JOIN IN ORACLE SQL
WHERE D.DEPARTMENT_ID = S.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
ORDER BY STAFF_COUNT DESC

Oracle SQL query for result without the department name, just the department_id

SELECT S.DEPARTMENT_ID,COUNT(S.DEPARTMENT_ID) AS STAFF_COUNT FROM STAFF S
GROUP BY S.DEPARTMENT_ID
ORDER BY STAFF_COUNT DESC

Hope this helps. Cheers.

Upvotes: 0

Robert Kock
Robert Kock

Reputation: 6018

You're almost there.
Order the result in descending order on the number of students and then take the first row:

SELECT department_name
FROM
(
  SELECT   d.department_name,
           COUNT(*) AS nr_students
  FROM     department d
  JOIN     student    s
    ON     d.department_id = s.department_id
  GROUP BY d.department_name
  ORDER BY nr_students DESC
)
WHERE ROWNUM <= 1;

Upvotes: 1

Related Questions