How to solve error group function not allowed

user_5950826 0
Viewed 27

There are two tables:

  • employeee(emp_id,emp_name,salary,dept_id) and,
  • department(dept_id,dept_name).

The query is to find the name of department with maximum number of employees. The query I came up with is this

select dept_name from department
where dept_id = (select dept_id from (select dept_id,count(dept_id) numbers 
                 from employee group by dept_id)
                 where numbers = max(numbers));

The error displayed is this

ORA-00934: group function is not allowed here

Please throw some light here I have been trying this for more than two days.

oracle11g sql
2 Answers

You are trying to use the result of a subquery twice. In order to (re)use it multiple times you need to place it into a CTE (Common Table Expression). Once you do that the query becomes easier.

For example, you could rephrase your query as:

with
x as (
  select dept_id,count(dept_id) as numbers from employee group by dept_id
),
y as (
  select dept_id from x where numbers = (select max(numbers) from x)
)
select dept_name 
from department d
join y on y.dept_id = d.dept_id
user_64361910

one solution is to order and after that take only the first record...

like this:

select * from
(
  select d.dept_name, count(e.id) 
  from department d, employee e
  where e.dept_id = d.dept_id
  group by d.dept_name
  order by count(e.id) desc
)
where rownum = 1;
user_92055960
Related