CODEWITHSUNDEEP
sqloracle-database
Vaishnavi Killekar
Vaishnavi Killekar

Reputation: 503

Find department with maximum number of employees in Oracle SQL

I have these two tables and I need to find the department name with maximum number of employees.

The other solutions were not for Oracle, so I'm posting this question. Also, it would be really helpful if the query can be explained thoroughly as I'm finding it hard to visualise it.

EMPLOYEE

EMPNO EMPNAME MANAGER SALARY DEPT_NO
1     Puja    6       30000  2
2     Purabi  1       15000  3
3     Barun   6       23000  2
4     Sudha   1       20000  1
5     Amal    2       20000  1
6     Rakesh  3       30000  4

DEPARTMENT

Dept_No Dept_Name  Location
1       Production LaneA
2       Marketing  LaneB
3       Sales      LaneC
4       HR         LaneD

So far I could manage getting the highest number of employees. So I was thinking if somehow I can write another sub-query where I count the employees in the departments again and compare them to the max_num_emp that I calculated in the first query.

This is the query which retrieves the maximum number of employees. It does not return the dept_no.

select count(dept_no)
from employee
group by dept_no
order by count(dept_no) desc
fetch first row only;

Expected output

DEPT_NAME
Production
Marketing

I can also add the dept_no column in the query, then I will have to somehow find out how to get the max and that was somehow giving me errors because the query was violating some rules. I had actually tried doing max(above query).

So I thought of just getting the maximum employee count and then determine all departments which have those many employees and display their name.

Upvotes: 1

Views: 4891

Answers (3)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You may have used FETCH..FIRST syntax using WITH TIES instead of ONLY.

SELECT d.dept_name
  FROM department d 
   JOIN employee e ON d.dept_no = e.dept_no
GROUP BY d.dept_name
ORDER BY COUNT(*) 
DESC FETCH FIRST 1 ROW WITH TIES ;

Demo

Upvotes: 2

forpas
forpas

Reputation: 164089

You have a working query which you need to join to the table department:

select d.Dept_Name   
from department d inner join (
  select dept_no
  from employee
  group by dept_no
  order by count(*) desc
  fetch first row only
) t
on t.dept_no = d.dept_no

Edit
Try this (I cannot try it):

select d.dept_name 
from department d inner join (
  select x.dept_no from (
    select dept_no, rank() over (order by count(*) desc) rn
    from employee
    group by dept_no
    order by count(dept_no) desc
  ) x
  where x.rn = 1
) t
on t.dept_no = d.dept_no

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If you are not looking for duplicates, then:

select d.dept_name, count(*)
from department d join
     employee e
     on d.dept_no = e.dept_no
group by d.dept_no, d.dept_name
order by count(dept_no) desc
fetch first row only;

Upvotes: 0

Related Questions