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.
