CODEWITHSUNDEEP
sqloracle-database
Oscar
Oscar

Reputation: 69

Error - not a single-group group function

I have this query:

select employee_id, max(count(employee_id))
from hr.job_history 
group by employee_id;

But it showns an error "ORA-00937: not a single-group group function". Do you know why?

Also doing the query a little different, limiting the rows, like this:

select count(employee_id) 
from hr.job_history 
group by employee_id order by count(employee_id) desc fetch first 2 rows only;

It shows an error : "ORA-00933: SQL command not properly ended". Do you know why? Thanks!

Upvotes: 2

Views: 1500

Answers (2)

GMB
GMB

Reputation: 222442

You can't nest aggregate functions in the same scope. The fetch clause is available in Oracle starting version 12c only, and you seem to be running an earlier version. An alternative uses window functions. This gives you the employee(s) with most rows in job_history, including ties if any:

select *
from (
    select employee_id, count(employee_id) cnt,
        rank() over(order by count(employee_id) desc) rn
    from hr.job_history 
    group by employee_id
) t
where rn = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Try using a subquery:

select e.*
from (select employee_id, count(employee_id)
      from hr.job_history 
      group by employee_id
      order by count(*) desc
     ) e
where rownum = 1;

My guess is that your version of Oracle may not support fetch.

Upvotes: 2

Related Questions