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

Answer 1

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>