Oracle ORA-00937 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00937: Not a Single-Group Group Function

ORA-00937 is one of the most common SQL errors in Oracle, occurring when you mix aggregate functions (like SUM, COUNT, AVG) with non-aggregated columns in a SELECT statement without a proper GROUP BY clause. Oracle cannot simultaneously return a single aggregated value for the whole table and individual row-level values for a regular column, so it throws this error to alert the developer. Understanding this error is fundamental to writing correct Oracle SQL queries.

Top 3 Causes

1. Missing GROUP BY Clause

Using an aggregate function alongside a plain column without any GROUP BY clause is the most frequent trigger.

-- WRONG: Missing GROUP BY
SELECT department_id, COUNT(*) AS emp_count
FROM employees;
-- ORA-00937: not a single-group group function

-- CORRECT: Add GROUP BY
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

2. Incomplete GROUP BY Clause

Even when GROUP BY is present, every non-aggregated column in the SELECT list must appear in the GROUP BY clause. If even one column is missing, Oracle raises ORA-00937.

-- WRONG: job_id is missing from GROUP BY
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
-- ORA-00937: not a single-group group function

-- CORRECT: Include all non-aggregate columns
SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, job_id;

3. Nested Aggregate Functions Used Directly

Trying to nest aggregate functions directly (e.g., MAX(AVG(...))) without a subquery also triggers this error. Oracle does not allow direct nesting of group functions at the same query level.

-- WRONG: Direct nesting of aggregate functions
SELECT department_id, MAX(AVG(salary))
FROM employees
GROUP BY department_id;
-- ORA-00937: not a single-group group function

-- CORRECT: Use a subquery
SELECT MAX(avg_salary) AS max_avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
);

Quick Fix Solutions

Add or complete the GROUP BY clause: Ensure every non-aggregated column in SELECT is listed in GROUP BY.
Use subqueries for nested aggregates: Break complex aggregations into an inner query, then apply the outer aggregate on top.
Use analytic (window) functions: When you need both row-level and aggregated data together, use OVER (PARTITION BY ...) instead of standard group functions.

-- Using analytic function to show salary vs. department average
SELECT employee_id,
       department_id,
       salary,
       ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS dept_avg
FROM employees
ORDER BY department_id;

Prevention Tips

Follow the SELECT-GROUP BY column matching rule: Make it a habit — every non-aggregated column in SELECT must appear in GROUP BY. Double-check this before running any grouped query.
Use SQL linting tools: Tools like SQL Developer, Toad, or DBeaver catch syntax issues before execution. Always validate queries in a development environment before deploying to production to avoid runtime surprises.

Related Errors

Error Code	Description
ORA-00979	`not a GROUP BY expression` — column in SELECT/HAVING not in GROUP BY
ORA-00934	`group function is not allowed here` — aggregate used in WHERE clause
ORA-00935	`group function is nested too deeply` — excessive nesting of aggregates

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

DEV Community