ORA-00937 Not a Single-Group Group Function - Complete Resolution Guide

ORA-00937: Not a Single-Group Group Function

Error Overview

Error Text: ORA-00937: not a single-group group function

The ORA-00937 error occurs when you use an aggregate function (SUM, COUNT, AVG, MAX, MIN, etc.) in a SELECT statement along with non-aggregated columns, but without a proper GROUP BY clause. This is one of the most common SQL syntax errors encountered by Oracle developers.

Understanding the Error

When you use an aggregate function, Oracle needs to know how to group the data:

• Single-group query: Returns one row (no GROUP BY, all columns aggregated)
• Multi-group query: Returns multiple rows (GROUP BY specified)

The error occurs when your query is ambiguous - mixing aggregated and non-aggregated columns without telling Oracle how to group them.

Common Causes

1. Missing GROUP BY Clause

• Non-aggregated columns in SELECT with aggregate functions
• Forgetting to add GROUP BY after adding new columns

2. Incomplete GROUP BY

• Not all non-aggregated columns listed in GROUP BY
• Column aliases used instead of expressions

3. Aggregate in Wrong Context

• Using aggregate functions in WHERE clause
• Misunderstanding of single-row vs. multi-row context

Error Examples and Solutions

Example 1: Missing GROUP BY

-- ERROR: Non-aggregated column with aggregate function
SELECT department_id, COUNT(*)
FROM employees;
-- ORA-00937: not a single-group group function

-- SOLUTION 1: Add GROUP BY for the non-aggregated column
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- SOLUTION 2: Remove non-aggregated column (single-group query)
SELECT COUNT(*)
FROM employees;

-- SOLUTION 3: Aggregate the other column too
SELECT MAX(department_id), COUNT(*)
FROM employees;

Example 2: Multiple Non-Aggregated Columns

-- ERROR: Multiple columns without complete GROUP BY
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id;
-- ORA-00937: not a single-group group function

-- SOLUTION: Include ALL non-aggregated columns in GROUP BY
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

Example 3: Expression in SELECT vs GROUP BY

-- ERROR: Expression mismatch
SELECT TRUNC(hire_date, 'MONTH'), COUNT(*)
FROM employees
GROUP BY hire_date;
-- ORA-00937: not a single-group group function

-- SOLUTION: GROUP BY must match the SELECT expression exactly
SELECT TRUNC(hire_date, 'MONTH'), COUNT(*)
FROM employees
GROUP BY TRUNC(hire_date, 'MONTH');

-- Alternative: Use column alias with subquery
SELECT hire_month, COUNT(*)
FROM (
  SELECT TRUNC(hire_date, 'MONTH') as hire_month
  FROM employees
)
GROUP BY hire_month;

Example 4: CASE Expression in SELECT

-- ERROR: CASE expression not in GROUP BY
SELECT
  CASE WHEN salary > 10000 THEN 'High' ELSE 'Low' END as salary_band,
  COUNT(*)
FROM employees;
-- ORA-00937: not a single-group group function

-- SOLUTION: Include CASE expression in GROUP BY
SELECT
  CASE WHEN salary > 10000 THEN 'High' ELSE 'Low' END as salary_band,
  COUNT(*)
FROM employees
GROUP BY CASE WHEN salary > 10000 THEN 'High' ELSE 'Low' END;

Example 5: Subquery with Aggregates

-- ERROR: Mixing row-level and aggregated data
SELECT
  employee_id,
  salary,
  (SELECT AVG(salary) FROM employees) as avg_salary
FROM employees
WHERE salary > AVG(salary);  -- Can't use aggregate in WHERE
-- ORA-00937 or ORA-00934

-- SOLUTION: Use HAVING or subquery
SELECT
  employee_id,
  salary,
  (SELECT AVG(salary) FROM employees) as avg_salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Or use analytic function
SELECT
  employee_id,
  salary,
  AVG(salary) OVER () as avg_salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Example 6: Window Functions vs Aggregates

-- Sometimes you want row-level data WITH aggregates
-- Use analytic (window) functions instead

-- ERROR approach:
SELECT employee_id, department_id, salary, AVG(salary)
FROM employees;
-- ORA-00937

-- SOLUTION: Use analytic function
SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary) OVER () as overall_avg,
  AVG(salary) OVER (PARTITION BY department_id) as dept_avg
FROM employees;

Diagnostic Approach

Step 1: Identify All Columns

-- List all columns in your SELECT
-- Mark each as:
-- [A] = Aggregated (inside SUM, COUNT, AVG, MAX, MIN, etc.)
-- [N] = Non-aggregated (plain column reference)

SELECT
  department_id,          -- [N] Non-aggregated
  job_id,                 -- [N] Non-aggregated
  COUNT(*),               -- [A] Aggregated
  SUM(salary),            -- [A] Aggregated
  MAX(hire_date)          -- [A] Aggregated
FROM employees;

-- All [N] columns MUST appear in GROUP BY

Step 2: Build GROUP BY

-- Start with the columns marked [N]
GROUP BY department_id, job_id

-- Final correct query:
SELECT
  department_id,
  job_id,
  COUNT(*),
  SUM(salary),
  MAX(hire_date)
FROM employees
GROUP BY department_id, job_id;

Common Patterns

Pattern 1: Summary with Detail

-- Show each row with its group summary
-- Use analytic functions, not GROUP BY

SELECT
  employee_id,
  last_name,
  department_id,
  salary,
  SUM(salary) OVER (PARTITION BY department_id) as dept_total,
  COUNT(*) OVER (PARTITION BY department_id) as dept_count,
  ROUND(salary / SUM(salary) OVER (PARTITION BY department_id) * 100, 2) as pct_of_dept
FROM employees
ORDER BY department_id, salary DESC;

Pattern 2: Top N per Group

-- Get top 3 earners per department
SELECT *
FROM (
  SELECT
    department_id,
    employee_id,
    last_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
  FROM employees
)
WHERE rn <= 3
ORDER BY department_id, rn;

Pattern 3: Running Totals

-- Running total without GROUP BY issues
SELECT
  employee_id,
  hire_date,
  salary,
  SUM(salary) OVER (ORDER BY hire_date) as running_total
FROM employees
ORDER BY hire_date;

Pattern 4: Aggregates with Filtering

-- Filter aggregated results with HAVING (not WHERE)
SELECT
  department_id,
  COUNT(*) as emp_count,
  AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5      -- Filter on aggregate
   AND AVG(salary) > 5000
ORDER BY avg_salary DESC;

Key Rules to Remember

Rule 1: GROUP BY Requirements

Every non-aggregated column in SELECT must appear in GROUP BY.

Rule 2: Expression Matching

GROUP BY expressions must match SELECT expressions exactly.

Rule 3: WHERE vs HAVING

• WHERE filters rows BEFORE grouping (no aggregates allowed)
• HAVING filters groups AFTER grouping (aggregates allowed)

Rule 4: When You Need Row-Level Data

Use analytic (window) functions instead of GROUP BY.

Quick Reference

Scenario	Solution
Need total count only	SELECT COUNT(*) FROM table
Need count per category	SELECT category, COUNT(*) FROM table GROUP BY category
Need each row + total	Use analytic function: COUNT(*) OVER ()
Need each row + category total	Use partition: COUNT(*) OVER (PARTITION BY category)
Filter on aggregate	Use HAVING, not WHERE

Common Mistakes

Mistake 1: Using Column Alias in GROUP BY

-- ERROR in Oracle (alias not recognized in GROUP BY)
SELECT TRUNC(hire_date, 'YYYY') as hire_year, COUNT(*)
FROM employees
GROUP BY hire_year;  -- Can't use alias!

-- CORRECT: Repeat the expression
SELECT TRUNC(hire_date, 'YYYY') as hire_year, COUNT(*)
FROM employees
GROUP BY TRUNC(hire_date, 'YYYY');

Mistake 2: Aggregate in WHERE

-- ERROR: Can't use aggregate in WHERE
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 5000  -- Wrong!
GROUP BY department_id;

-- CORRECT: Use HAVING for aggregate conditions
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

Mistake 3: Forgetting New Columns

-- Original working query
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- After adding a column - forgot to update GROUP BY
SELECT department_id, job_id, COUNT(*)  -- Added job_id
FROM employees
GROUP BY department_id;  -- Forgot to add job_id!

-- CORRECT
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id;

Related Errors

• ORA-00934 - Group function not allowed here
• ORA-00979 - Not a GROUP BY expression
• ORA-30483 - Window functions not allowed here
• ORA-00932 - Inconsistent datatypes

Summary

1. Every non-aggregated SELECT column needs GROUP BY
2. Expressions in SELECT must match GROUP BY exactly
3. Use HAVING for aggregate filters, not WHERE
4. Use analytic functions when you need row-level + aggregate data
5. Can’t use column aliases in GROUP BY (use full expression)