ORA-00978: nested group function without GROUP BY

ORA-00978: nested group function without GROUP BY

Oracle PL/SQL error message: ORA-00978: nested group function without GROUP BY.

Cause:

A group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used within another group function, as in MAX(COUNT(*)), without a corresponding GROUP BY clause.

Solution:

Either add a GROUP BY clause or remove the extra level of nesting.

Example:

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

Output:

ORA-00978: nested group function without GROUP BY

Correct:

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