Error - not a single-group group function

Question

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!

Check https://stackoverflow.com/questions/1795198/sql-not-a-single-group-group-function for your first question — Gokul Panigrahi, Oct 20 '20 at 14:54

score 1 · Accepted Answer · answered Oct 20 '20 at 14:53

1

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.

answered Oct 20 '20 at 14:53

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Oct 20 '20 at 14:56

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

Error - not a single-group group function

2 Answers2