How to fix group function nested too deeply error-CodePudding

I've run into an error I've never seen before while trying to learn SQL where it is saying that my group by function is nested too deeply? Any ideas on how to fix this code would be greatly appreciated. I am trying to find the division with the most employees.

Tables:

division(DID, dname, managerID)
employee(empid, name, salary, DID)

Code:

select dname 
from division
where did in (select did from employee group by did having max(count(empid)))

CodePudding user response：

I don't have your tables, but Scott's sample schema contains similar tables - dept (departments) and emp (employees who work in these departments).

How many employees work in each department?

SQL> select deptno, count(*)
  2  from emp
  3  group by deptno
  4  order by 2 desc;

    DEPTNO   COUNT(*)
---------- ----------
        30          6    --> this is what you want, it has the most employees
        20          5
        10          3

Which department is it?

SQL> select * from dept order by deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO             --> it's SALES
        40 OPERATIONS     BOSTON

SQL>

This is what you tried: not that it won't work (Oracle doesn't let you), syntax is wrong. having clause should be something like having count(*) = 25, not just your wishes comes true (having max(count(*)) which reads as "give me department that has maximum count of employees").

SQL> select dname
  2  from dept
  3  where deptno in (select deptno from emp
  4                   group by deptno
  5                   having max(count(empno))
  6                  );
                 having max(count(empno))
                            *
ERROR at line 5:
ORA-00935: group function is nested too deeply


SQL>

So, what can we do about it? A simple option is to rank departments by number of employees:

SQL> select deptno,
  2         count(*) cnt,
  3         rank() over (order by count(*) desc) rnk
  4  from emp
  5  group by deptno;

    DEPTNO        CNT        RNK
---------- ---------- ----------
        30          6          1      --> department 30 ranks as the highest
        20          5          2
        10          3          3

SQL>

The rest is easy: use that query as a subquery (or a CTE, as I did), and select the one that ranks the highest:

SQL> with temp as
  2    (select deptno,
  3            count(*) cnt,
  4            rank() over (order by count(*) desc) rnk
  5     from emp
  6     group by deptno
  7    )
  8  select d.dname
  9  from dept d join temp t on t.deptno = d.deptno
 10  where t.rnk = 1;         --> the highest rank

DNAME
--------------
SALES

SQL>

SALES it is.