DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00934 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00934: Group Function is Not Allowed Here

ORA-00934 is one of the most common Oracle SQL errors, occurring when an aggregate function (such as SUM(), AVG(), COUNT(), MAX(), or MIN()) is used in a clause that does not support it. Oracle's SQL engine evaluates clauses in a specific logical order, and aggregate functions can only be used where grouped data is available — primarily in the SELECT and HAVING clauses.

Top 3 Causes

1. Using an Aggregate Function in the WHERE Clause

The most frequent cause. Developers often try to filter rows based on an aggregate value directly in WHERE, but at that stage, Oracle hasn't grouped the data yet.

Incorrect:

-- This will throw ORA-00934
SELECT employee_id, salary
FROM employees
WHERE salary > AVG(salary);
Enter fullscreen mode Exit fullscreen mode

Fix — Use a subquery:

-- Correct: calculate aggregate in a subquery
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Enter fullscreen mode Exit fullscreen mode

2. Using an Aggregate Function in the GROUP BY Clause

GROUP BY defines the grouping keys and only accepts raw columns or non-aggregate expressions. Placing an aggregate function here causes ORA-00934.

Incorrect:

-- This will throw ORA-00934
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id, SUM(salary);
Enter fullscreen mode Exit fullscreen mode

Fix — Move aggregate condition to HAVING:

-- Correct: use HAVING for aggregate filtering
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000;
Enter fullscreen mode Exit fullscreen mode

3. Using an Aggregate Function in CONNECT BY or START WITH

Hierarchical queries process rows individually, so aggregate functions are not permitted in CONNECT BY or START WITH clauses.

Incorrect:

-- This will throw ORA-00934
SELECT employee_id, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
       AND salary > AVG(salary);
Enter fullscreen mode Exit fullscreen mode

Fix — Pre-calculate the aggregate in a subquery:

-- Correct: isolate aggregate logic
SELECT e.employee_id, e.manager_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees)
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Wrong Clause Correct Approach
WHERE AGG_FUNC() Use subquery or CTE
GROUP BY AGG_FUNC() Move condition to HAVING
CONNECT BY AGG_FUNC() Pre-calculate via subquery

Prevention Tips

  • Memorize SQL logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This single rule tells you exactly where aggregate functions are allowed (HAVING, SELECT) and where they are not (WHERE, GROUP BY, CONNECT BY).

  • Use IDE syntax checking: Tools like SQL Developer, Toad, or DBeaver highlight this error in real time before you execute the query. Always validate complex queries in a development environment before deploying to production.

Related Oracle Errors

  • ORA-00935: Group function nested too deeply.
  • ORA-00937: Not a single-group group function — mixing non-aggregated columns with aggregate functions without GROUP BY.
  • ORA-00979: Not a GROUP BY expression — a SELECT column is missing from the GROUP BY clause.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)