DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00935 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00935: Group Function Is Nested Too Deeply

ORA-00935 is thrown by Oracle when you attempt to nest aggregate (group) functions more levels deep than Oracle permits in a single query block. In a standard SELECT statement, Oracle allows a maximum of two levels of group function nesting (e.g., MAX(SUM(...))); going beyond that causes this error. It most commonly surfaces when writing complex reporting or statistical queries where developers stack multiple aggregate functions without separating them into subqueries.

Top 3 Causes

1. Three or More Levels of Nested Aggregate Functions

Oracle strictly limits aggregate nesting to two levels in a single query block. Attempting MAX(SUM(AVG(...))) exceeds this limit and triggers ORA-00935.

-- ❌ Causes ORA-00935: three levels of nesting
SELECT MAX(SUM(AVG(salary)))
FROM employees
GROUP BY department_id;

-- ✅ Fix: separate each aggregation level using subqueries
SELECT MAX(dept_sum)
FROM (
    SELECT SUM(avg_sal) AS dept_sum
    FROM (
        SELECT department_id, AVG(salary) AS avg_sal
        FROM employees
        GROUP BY department_id
    )
);
Enter fullscreen mode Exit fullscreen mode

2. Multi-Level Aggregation Without Subqueries or Inline Views

Trying to compute a "summary of a summary" in a single query block — without using an inline view or CTE — will cause Oracle to raise ORA-00935. Each aggregation level must be isolated in its own query scope.

-- ❌ Causes ORA-00935
SELECT department_id, MAX(SUM(salary))
FROM employees
GROUP BY department_id;

-- ✅ Fix using WITH clause (CTE)
WITH dept_totals AS (
    SELECT department_id,
           SUM(salary) AS dept_salary
    FROM employees
    GROUP BY department_id
)
SELECT department_id, MAX(dept_salary) OVER () AS company_max,
       dept_salary
FROM dept_totals;
Enter fullscreen mode Exit fullscreen mode

3. Improper Nesting in HAVING or ORDER BY Clauses

Developers sometimes place nested group functions inside HAVING or ORDER BY clauses, assuming Oracle will resolve them against already-aggregated results. Oracle does not support this and will raise ORA-00935.

-- ❌ Causes ORA-00935 in HAVING clause
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING MAX(SUM(salary)) > 50000;

-- ✅ Fix: push aggregation into a subquery
SELECT department_id, dept_total
FROM (
    SELECT department_id,
           SUM(salary) AS dept_total
    FROM employees
    GROUP BY department_id
)
WHERE dept_total > 50000;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Use inline views or subqueries to break multi-level aggregation into separate, independent steps.
  • Use CTEs (WITH clause) for cleaner, more readable multi-step aggregation — available from Oracle 9i onward.
  • Leverage Oracle Window Functions (SUM() OVER(), MAX() OVER()) as a powerful alternative that avoids nesting altogether. 
-- Window function alternative — no nesting required
SELECT
    department_id,
    SUM(salary)                              AS dept_total,
    MAX(SUM(salary)) OVER ()                 AS company_max_dept_total
FROM employees
GROUP BY department_id;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Design aggregation levels before coding. Sketch out your aggregation hierarchy (row-level → group-level → summary-level) and implement each level as a separate CTE or subquery. This keeps logic clean and avoids accidental over-nesting.

  2. Establish a team coding standard. Enforce a rule that any query requiring more than two levels of aggregation must use CTEs or window functions. Code reviews should explicitly check for deeply nested group functions. This prevents ORA-00935 and improves overall query maintainability.

Related Oracle Errors

Error Code Description
ORA-00934 Group function not allowed here (e.g., in WHERE clause)
ORA-00937 Not a single-group group function (missing GROUP BY)
ORA-00979 Not a GROUP BY expression (column missing from GROUP BY)
ORA-30483 Window functions not allowed here (wrong placement of analytic functions)

📖 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)