How to Fix ORA-00934: Group Function Is Not Allowed Here in Oracle

ORA-00934 means an aggregate function such as SUM, AVG, or COUNT appears in a part of a statement where groups do not exist yet, most often the WHERE clause. WHERE filters single rows before any grouping happens, so it cannot test an average or a total. Conditions on aggregates belong in HAVING, or in a subquery that computes the aggregate first.

Reproduce the Error

Example:

create table demo_scores (player varchar2(10), score number);
insert into demo_scores values ('Ana', 70);
insert into demo_scores values ('Ana', 90);
insert into demo_scores values ('Raj', 40);
commit;

select player, avg(score) from demo_scores where avg(score) > 50 group by player;
select player from demo_scores group by sum(score);

Output:

Table DEMO_SCORES created.

1 row inserted.

1 row inserted.

1 row inserted.

Commit complete.

Error starting at line : 7
In command -
select player, avg(score) from demo_scores where avg(score) > 50 group by player
Error at Command Line : 7 Column : 50
Error report -
SQL Error: ORA-00934: group function is not allowed here

Error starting at line : 8
In command -
select player from demo_scores group by sum(score)
Error at Command Line : 8 Column : 41
Error report -
SQL Error: ORA-00934: group function is not allowed here

The first query filters on AVG in WHERE; the second groups by an aggregate, which is not allowed either.

Fix: Use HAVING or a Subquery

Example:

-- conditions on aggregates go in HAVING
select player, avg(score) as average
from   demo_scores
group  by player
having avg(score) > 50;

-- rows above the overall average: compare with a subquery
select player, score from demo_scores
where  score > (select avg(score) from demo_scores);

Output:

PLAYER       AVERAGE
_________ __________
Ana               80

PLAYER       SCORE
_________ ________
Ana             70
Ana             90

• HAVING filters groups after aggregation: only Ana averages above 50.
• To compare rows with an aggregate over all rows, compute it in a subquery: scores above the overall average.

Things to Know

• Aggregates are allowed in the select list, HAVING, and ORDER BY of a grouped query, not in WHERE or GROUP BY.
• Analytic functions, such as AVG(score) OVER (), in WHERE raise ORA-00934 too; filter on them in an outer query.
• UPDATE ... SET column = SUM(...) raises the same error; use a subquery that returns the aggregate.

Related Guides

• How to Filter Groups with HAVING in Oracle SQL
• How to Fix ORA-00979: Not a GROUP BY Expression in Oracle

Conclusion

ORA-00934 means an aggregate sits where rows are still single rows, usually in WHERE. Move the condition to HAVING to filter groups, or compute the aggregate in a subquery to compare rows with it.