ORA-00934: group function is not allowed here

ORA-00934: group function is not allowed here

Oracle PL/SQL error message: ORA-00934: group function is not allowed here.

Cause:

One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause.

Solution:

Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING clause.

Example:

select s.student_id, s.first_name, 
s.last_name, count(o.order_id)
from students s, orders o 
where s.student_id = o.student_id
group by s.student_id, s.first_name, 
s.last_name, count(o.order_id)
order by s.student_id;

Output:

ORA-00934: group function is not allowed here

Correct:

select s.student_id, s.first_name, 
s.last_name, count(o.order_id)
from students s, orders o 
where s.student_id = o.student_id
group by s.student_id, s.first_name, 
s.last_name
order by s.student_id;