How to find the department with the most employees in Oracle SQL

Question

I am trying to find the department with the most employees in the demo HR schema:

SELECT d.DEPARTMENT_ID, d.DEPARTMENT_NAME, MAX(COUNT(e.EMPLOYEE_ID))
FROM departments d INNER JOIN employees e ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAME

The script works without MAX().

Tim Biegeleisen · Answer 1 · 2016-01-05T09:10:04.897

2

You can wrap your current query (minus the MAX) and select the department group with the largest head count.

WITH CTE (DEPARTMENT_ID, DEPARTMENT_NAME, empCount) AS
(
    SELECT d.DEPARTMENT_ID, d.DEPARTMENT_NAME, COUNT(e.EMPLOYEE_ID) AS empCount
    FROM departments d INNER JOIN employees e ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
    GROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAME
)
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM CTE
WHERE empCount = (SELECT MAX(t.empCount) FROM CTE t)

edited Jan 05 '16 at 09:10

answered Jan 05 '16 at 01:10

Tim Biegeleisen

502,043
27
286
360

1

There is no `LIMIT` keyword in Oracle. – Bob Jarvis - Слава Україні Jan 05 '16 at 03:17
@BobJarvis I thought that he had tagged with MySQL. In any case, my first answer stands and should work on Oracle. – Tim Biegeleisen Jan 05 '16 at 03:31
@TimBiegeleisen It is syntactically incorrect. You need to add a group by to the outer query as well. Because, you are using MAX as aggregate. Else, it will throw `ORA-00937: not a single-group group function` – Lalit Kumar B Jan 05 '16 at 08:28

Madhusudana Reddy Sunnapu · Answer 2 · 2016-01-05T05:24:48.340

You can try fetching the departments with order by desc on count(e.EMPLOYEE_ID) and using the ROWNUM by limiting the results only to the first row as below :

select * from (select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, count(e.EMPLOYEE_ID) cnt 
from departments d inner join employees e on d.DEPARTMENT_ID = e.DEPARTMENT_ID 
group by d.DEPARTMENT_ID, d.DEPARTMENT_NAME order by 3 desc) where ROWNUM=1

Frankly, I couldn't test this query against Oracle DB as I only have Derby DB installed on my machine and this doesn't support ROWNUM psuedo-column.

score 1 · Answer 3 · answered Jan 05 '16 at 09:59

Please find the query:

SELECT *
FROM
(
SELECT d.DEPARTMENT_ID, d.DEPARTMENT_NAME ,COUNT(e.EMPLOYEE_ID) EMP_COUNT
FROM HR.departments d
INNER JOIN HR.employees e
ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_ID, d.DEPARTMENT_NAME
ORDER BY EMP_COUNT DESC
)
WHERE ROWNUM=1

Rahul · Answer 4 · 2016-01-05T03:48:51.850

0

Use an ORDER BY clause like

select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, 
count(e.EMPLOYEE_ID) as employe_count
from departments d
inner join employees e
on d.DEPARTMENT_ID = e.DEPARTMENT_ID
group by d.DEPARTMENT_ID, d.DEPARTMENT_NAME
order by count(e.EMPLOYEE_ID) desc
offset 0 rows fetch next 1 rows only;

Note: OFFSET and FETCH NEXT are only available starting with Oracle 12.1

edited Jan 05 '16 at 03:48

answered Jan 05 '16 at 01:11

Rahul

76,197
13
71
125

Note that `OFFSET` and `FETCH NEXT` are only available starting with Oracle 12.1. – Bob Jarvis - Слава Україні Jan 05 '16 at 03:14
@BobJarvis, Yes, mentioned that in answer. See edit. Thanks. – Rahul Jan 05 '16 at 03:48

Lalit Kumar B · Answer 5 · 2016-01-05T08:34:18.397

You could use the COUNT() OVER() analytic function.

For example, using the standard employees table in HR schema:

SQL> SELECT DISTINCT department_id,
  2    COUNT(department_id) OVER(PARTITION BY department_id) rn
  3  FROM hr.employees t
  4  ORDER BY rn DESC;

DEPARTMENT_ID         RN
------------- ----------
           50         45
           80         34
           30          6
          100          6
           60          5
           90          3
           20          2
          110          2
           10          1
           40          1
           70          1
                       0

12 rows selected.

SQL>

To find the department_id with maximum employees from above result set:

SQL> SELECT department_id
  2  FROM
  3    (SELECT department_id,
  4      row_number() OVER(ORDER BY rn DESC) rn
  5    FROM
  6      ( SELECT DISTINCT department_id,
  7        COUNT(department_id) OVER(PARTITION BY department_id) rn
  8      FROM hr.employees t
  9      )
 10    )
 11  WHERE rn = 1;

DEPARTMENT_ID
-------------
           50

SQL>

score 0 · Answer 6 · edited Apr 25 '21 at 00:10

0

select dname,deptno,loc from dept where deptno=(select deptno from 
(select deptno,count(*) as emp_count from emp group by deptno order by emp_count desc) where rownum=1)

edited Apr 25 '21 at 00:10

notAnkur

73
2
6

answered Jun 20 '17 at 15:41

Abhi

1

How to find the department with the most employees in Oracle SQL

6 Answers6