ORA-00978: nested group function without GROUP BY

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This type of error is easily explained and as an experienced SQL programmer you should see it only if you forget to append the GROUP BY clause to the SELECT statement:

select table_name, column_name, sum(data_length) l
from user_tab_columns;

ORA-00978: nested group function without GROUP BY

it's enough to add a proper GROUP BY to make it work:

select table_name, column_name, sum(data_length) l
from user_tab_columns
group by table_name, column_name;

For a slightly less obvious situation, see the posting concerning ORA-00937.

Aggregate functions do not necessarily need the presence of GROUP BY though:

select min(data_length), max(data_length), avg(data_length) l
from user_tab_columns;