CODEWITHSUNDEEP
sqloracle11g
tnxy
tnxy

Reputation: 41

How to solve error group function not allowed

There are two tables:

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.

Upvotes: 2

Views: 59

Answers (2)

maikito
maikito

Reputation: 26

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;

Upvotes: 1

The Impaler
The Impaler

Reputation: 48850

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

Upvotes: 1

Related Questions