Need to create procedure to find dept with most emps

Question

I need to create a procedure to find the department name of the department with the most employees.

I am not allowed to use temp. tables as those were not seen in my course.

Code below

CREATE OR REPLACE PROCEDURE grootste_dept
IS    v_department    departments.department_name%type;
BEGIN
    SELECT  department_name
    INTO v_department
    FROM departments d
    JOIN employees e
    ON d.department_id = e.department_id
    GROUP BY department_name
    HAVING    COUNT(employee_id) = MAX(COUNT(employee_id);
DBMS_OUTPUT.PUT_LINE(v_department);
END;
/

Expected value = dept_name from dept with most emps

4/1      PL/SQL: SQL Statement ignored

10/33    PL/SQL: ORA-00935: group function is nested too deeply

Have a look at https://stackoverflow.com/questions/20035212/sql-group-function-nested-too-deeply for a very similar problem and some good ideas how to resolve it — Shaun Peterson, Aug 18 '19 at 23:21

score 0 · Accepted Answer · answered Aug 19 '19 at 05:18

One option is to rank departments on number of employees and return the one that is ranked as #1.

SQL> create or replace procedure grootste_dept is
  2    v_dname dept.dname%type;
  3  begin
  4    with data as
  5      (select d.dname,
  6              count(*) cnt,
  7              rank() over (order by count(*) desc ) rn
  8       from dept d join emp e on e.deptno = d.deptno
  9       group by d.dname
 10      )
 11    select t.dname
 12      into v_dname
 13      from data t
 14      where t.rn = 1;
 15
 16    dbms_output.put_line(v_dname);
 17
 18  exception
 19    when too_many_rows then
 20      dbms_output.put_line('Two or more departments have the same number of employees');
 21  end grootste_dept;
 22  /

Procedure created.

SQL> begin
  2    grootste_dept;
  3  end;
  4  /
SALES

PL/SQL procedure successfully completed.

SQL>

score 0 · Answer 2 · answered Aug 19 '19 at 08:36

You can use count(*) keep ( dense_rank first order by 1 desc ) over ( partition by e.department_id ) within a simple loop which returns the desired department name as exiting the loop :

SQL> set serveroutput on
SQL> create or replace procedure 
                       grootste_dept( o_department out departments.department_name%type ) is
begin
  for c in
    (
     select d.department_name
       from employees e
       join departments d on d.department_id = e.department_id
      order by count(*) keep ( dense_rank first order by 1 desc ) 
                        over ( partition by e.department_id )
    )
    loop
      o_department := c.department_name;
      dbms_output.put_line(o_department);
    end loop;
end;
/

SQL> var o_department varchar2(100);

SQL> begin
    grootste_dept(o_department => :o_department);
end;
/
PL/SQL procedure successfully completed

o_department
---------
<theDesiredDeptName>

If your DB was 12c, you'd get the desired Department name by making use of fetch first 1 row only :

select d.dname     
  into o_department   
  from employees e
  join dept d on d.deptno = e.deptno
 order by count(*) keep (dense_rank first order by 1 ) over ( partition by e.deptno) desc
 fetch first 1 row only

without using any subquery of looping statement.

Need to create procedure to find dept with most emps

2 Answers2