ORA-00934: Group Function Not Allowed - Fix Aggregate SQL

ORA-00934: Group Function Not Allowed Here

Error Overview

Error Text: ORA-00934: group function is not allowed here

The ORA-00934 error is raised when an aggregate (group) function — such as SUM, AVG, COUNT, MAX, or MIN — is used in a clause of a SQL statement that does not permit aggregate functions. The most common triggers are placing an aggregate in a WHERE clause, a JOIN ON condition, a GROUP BY expression, or a CHECK constraint.

Common Causes

1. Aggregate Function in WHERE Clause

Using WHERE SUM(salary) > 100000 instead of HAVING SUM(salary) > 100000
Filtering aggregated results before grouping has occurred

2. Aggregate Function in GROUP BY Clause

Attempting to group by the result of an aggregate: GROUP BY SUM(salary)
This is circular because GROUP BY runs before aggregation

3. Aggregate Function in JOIN ON Condition

ON e.dept_id = MAX(d.dept_id) — aggregates cannot appear in join predicates

4. Aggregate in CHECK Constraint

CHECK (SUM(quantity) <= 1000) — constraints operate row-by-row and cannot reference aggregations

5. Aggregate in ORDER BY of a Non-Aggregating Subquery

Nesting aggregates without proper GROUP BY alignment

Diagnostic Queries

Find Statements Using Aggregates Incorrectly

-- Search shared pool for statements that include aggregate keywords
-- and failed to execute
SELECT
  sql_id,
  sql_text,
  parse_calls,
  executions,
  last_active_time
FROM v$sql
WHERE parse_calls > 0
  AND executions = 0
  AND (UPPER(sql_text) LIKE '%SUM(%'
    OR UPPER(sql_text) LIKE '%AVG(%'
    OR UPPER(sql_text) LIKE '%COUNT(%')
  AND last_active_time > SYSDATE - 1/24
ORDER BY last_active_time DESC
FETCH FIRST 20 ROWS ONLY;

Check Audit Trail for ORA-00934 Events

SELECT
  event_timestamp,
  db_user_name,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE return_code = 934
  AND event_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY event_timestamp DESC;

Identify Invalid Objects That May Contain This Error

-- Check for invalid stored PL/SQL objects
SELECT
  object_name,
  object_type,
  last_ddl_time,
  status
FROM user_objects
WHERE status = 'INVALID'
ORDER BY last_ddl_time DESC;

View Compilation Errors for Invalid Objects

-- Get the exact error for an invalid procedure or package
SELECT
  name,
  type,
  line,
  position,
  text
FROM user_errors
WHERE status = 'INVALID' OR name = 'YOUR_OBJECT_NAME'
ORDER BY name, line, position;

Step-by-Step Resolution

1. Move Aggregate Filter from WHERE to HAVING

-- WRONG: Aggregate in WHERE clause
-- SELECT department_id, SUM(salary) AS total_sal
-- FROM employees
-- WHERE SUM(salary) > 100000
-- GROUP BY department_id;

-- CORRECT: Use HAVING for post-aggregation filtering
SELECT department_id, SUM(salary) AS total_sal
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

2. Combine WHERE and HAVING Correctly

When filtering on both raw columns and aggregates, use WHERE for raw filters and HAVING for aggregate filters:

-- WRONG: Mixing aggregate into WHERE
-- SELECT department_id, AVG(salary)
-- FROM employees
-- WHERE hire_date > DATE '2018-01-01' AND AVG(salary) > 60000
-- GROUP BY department_id;

-- CORRECT: Split the conditions
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date > DATE '2018-01-01'   -- row-level filter: goes in WHERE
GROUP BY department_id
HAVING AVG(salary) > 60000;           -- aggregate filter: goes in HAVING

3. Fix Subquery to Filter on Aggregate Result

When you need to filter against a specific aggregate value from another group, use a subquery:

-- WRONG: Aggregate in WHERE comparing to another aggregate
-- SELECT employee_id, salary
-- FROM employees
-- WHERE salary > AVG(salary);  -- ORA-00934: WHERE cannot contain aggregates

-- CORRECT: Subquery in WHERE clause
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

4. Fix Aggregate in GROUP BY

-- WRONG: Grouping by an aggregate expression
-- SELECT department_id, SUM(salary)
-- FROM employees
-- GROUP BY SUM(salary);  -- circular: aggregating then grouping by aggregate

-- CORRECT: Group by the dimension column
SELECT department_id, SUM(salary) AS total_sal
FROM employees
GROUP BY department_id;

5. Fix Aggregate in JOIN Condition

-- WRONG: Aggregate in ON clause
-- SELECT e.last_name, d.department_name
-- FROM employees e
-- JOIN departments d ON e.department_id = MAX(d.department_id);

-- CORRECT: Use a subquery to derive the value first
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id = (
  SELECT MAX(department_id) FROM departments
);

6. Fix UPDATE with Aggregate in WHERE

-- WRONG
-- UPDATE employees
-- SET salary = salary * 1.1
-- WHERE salary < AVG(salary);

-- CORRECT
UPDATE employees
SET salary = salary * 1.1
WHERE salary < (SELECT AVG(salary) FROM employees);

7. Fix Analytic Function as an Alternative

When you need row-level access to an aggregate value, use analytic (window) functions instead of group aggregates:

-- Find employees earning less than their department average
-- Using a subquery:
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE salary < (
  SELECT AVG(salary) FROM employees e2
  WHERE e2.department_id = employees.department_id
);

-- More efficient using analytic function:
SELECT employee_id, last_name, salary, department_id
FROM (
  SELECT
    employee_id,
    last_name,
    salary,
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
  FROM employees
)
WHERE salary < dept_avg;

Prevention Strategies

1. Understand Oracle’s Query Processing Order

-- Oracle processes clauses in this logical order:
-- 1. FROM / JOIN  — identify data sources
-- 2. WHERE        — filter individual rows (NO aggregates allowed)
-- 3. GROUP BY     — group filtered rows
-- 4. HAVING       — filter groups (aggregates ARE allowed)
-- 5. SELECT       — compute output expressions (aggregates ARE allowed)
-- 6. ORDER BY     — sort results (aggregates ARE allowed if in SELECT)

-- Rule of thumb:
-- "Before groups are formed?" → WHERE
-- "After groups are formed?" → HAVING
SELECT department_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date > DATE '2015-01-01'   -- pre-group filter: WHERE
GROUP BY department_id
HAVING COUNT(*) > 5                   -- post-group filter: HAVING
ORDER BY avg_sal DESC;

2. Use Common Table Expressions to Separate Aggregation

-- CTEs make aggregate vs. row-level separation explicit
WITH dept_totals AS (
  SELECT department_id, SUM(salary) AS total_sal, COUNT(*) AS headcount
  FROM employees
  GROUP BY department_id
)
SELECT
  d.department_name,
  dt.total_sal,
  dt.headcount
FROM dept_totals dt
JOIN departments d ON dt.department_id = d.department_id
WHERE dt.total_sal > 200000   -- this is a row filter on the aggregated CTE, valid
ORDER BY dt.total_sal DESC;

3. Enable PL/SQL Compile-Time Warnings

-- Catch issues in stored procedures at compile time
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

CREATE OR REPLACE PROCEDURE my_proc AS
BEGIN
  -- Bad SQL will fail to compile with clear error messages
  FOR r IN (SELECT dept_id, SUM(sal) FROM emp GROUP BY dept_id HAVING SUM(sal) > 0)
  LOOP
    NULL;
  END LOOP;
END;
/

4. SQL Development Standards

Always place aggregate filters in HAVING, never in WHERE
Use WHERE only for conditions on individual row columns
Use analytic functions when you need per-row access to aggregate context
Review every WHERE clause for aggregate function usage before code review

ORA-00979 - Not a GROUP BY expression
ORA-00936 - Missing expression
ORA-01427 - Single-row subquery returns more than one row
ORA-00907 - Missing right parenthesis

Emergency Response

Quick Fixes

Move aggregate condition to HAVING

-- Change: WHERE SUM(col) > n
-- To:     HAVING SUM(col) > n
-- (requires GROUP BY to also be present)

Wrap as a subquery

-- When you cannot add GROUP BY/HAVING (e.g., in a simple SELECT):
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Use analytic function to avoid separate subquery

SELECT * FROM (
  SELECT employee_id, salary,
         AVG(salary) OVER () AS overall_avg
  FROM employees
) WHERE salary > overall_avg;

Post-Resolution Cleanup

-- Verify fixed query produces expected row counts
SELECT department_id, SUM(salary), COUNT(*)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

-- Recompile any invalid stored procedures that referenced the bad SQL
ALTER PROCEDURE affected_proc COMPILE;

-- Check for remaining invalid objects
SELECT object_name, object_type, status
FROM user_objects
WHERE status = 'INVALID';