- Error code
- ORA-00937
- Product
- Oracle Database
- Versions
- Oracle Database 19c, 21c, 23ai and 26ai (same wording in every release)
- Category
- SQL Syntax & Query Structure
- Last reviewed
- 24 October 2026
On this page
What it means
ORA-00937 means a SELECT list mixes a group function such as COUNT, SUM or AVG with a plain column, and the query has no GROUP BY clause to say how the rows should be grouped.
A group function such as COUNT(*), SUM or MAX without a GROUP BY clause turns the whole result into one single group, so the query returns exactly one row. A plain column in the same SELECT list has a different value on every row, and Oracle cannot pick one of them for that single row. Oracle's reference states the rule directly: a SELECT list cannot include both a group function and an individual column expression unless that column is in a GROUP BY clause.
The message has the same wording in 19c, 21c and 26ai, and it names no column: look for the first non-aggregated column in the SELECT list. When the query already has a GROUP BY clause but a column is missing from it, Oracle raises a different error, ORA-00979. See ORDER BY, GROUP BY and HAVING explained for the grouping rules.
Common causes
- A per-group report such as orders per customer is written with COUNT(*) and the customer column, but the GROUP BY clause was forgotten.
- A total is added to an existing detail query (for example SUM(amount) next to each order line) without realising that it collapses the result to one row.
- Nested group functions such as MAX(AVG(salary)) return a single row for the whole query, so a grouped column cannot be selected next to them even when GROUP BY is present.
- A query is ported from MySQL, where a mix of aggregate and plain columns without GROUP BY may run when ONLY_FULL_GROUP_BY is off; Oracle always rejects it.
Diagnose it
Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.
Find the column that is not aggregated
Read the SELECT list and mark each expression as either inside a group function (COUNT, SUM, AVG, MIN, MAX, STDDEV, VARIANCE and so on) or plain. Constants and literals are fine. Every plain column is a column Oracle cannot place in the single-row result. SQL*Plus puts an asterisk under the first one it rejects.
Decide what the result should be: one row or one row per group
Run the query without the group functions to see the detail rows, then decide. If you want one row per customer, the plain columns belong in a GROUP BY clause. If you want a single grand total, the plain columns should go. If you want the total next to each detail row, you need an analytic function instead.
-- the detail rows behind the aggregate
SELECT customer_id, order_id, amount
FROM shop.orders
ORDER BY customer_id;Several rows per customer. Each distinct customer_id becomes one row once you add GROUP BY customer_id.
How to fix it
Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.
Add a GROUP BY clause with every plain column
Read-onlyThis is the fix Oracle's Action text gives: a GROUP BY clause that includes all individual column expressions in the SELECT list. You then get one row per group.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM shop.orders
GROUP BY customer_id
ORDER BY customer_id;Drop the plain column for a single grand total
Read-onlyIf you really want one row for the whole table, keep only group functions and constants in the SELECT list.
SELECT COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM shop.orders;Use an analytic function to show a total on every row
Read-onlyAdding OVER (...) turns an aggregate into an analytic function, which computes the value without collapsing the rows. Each order keeps its own row and also shows its customer's total, so no GROUP BY is needed.
SELECT order_id, customer_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM shop.orders
ORDER BY customer_id, order_id;With nested aggregates, compute the inner level in a subquery
Read-onlyTo find the department with the highest average salary, calculate the averages per department first, then pick the top row. That keeps department_id in the result, which MAX(AVG(salary)) cannot do.
SELECT department_id, avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM shop.employees
GROUP BY department_id
)
ORDER BY avg_salary DESC
FETCH FIRST 1 ROW ONLY;Examples
Counting orders per customer without GROUP BY
Not reproduced here: no Oracle Database is installed for this site. The result shows the message as documented in Oracle's error reference, which uses the same wording in 26ai, 21c and 19c. Adding GROUP BY customer_id fixes the query.
SELECT customer_id, COUNT(*) AS order_count
FROM orders;ORA-00937: not a single-group group function
A grouped column next to a nested aggregate
Not reproduced here: no Oracle Database is installed for this site. The message format is taken from Oracle's error reference. MAX(AVG(salary)) reduces all the department groups to one value, so department_id has no single value to show; the subquery fix above keeps it.
SELECT department_id, MAX(AVG(salary)) AS top_avg
FROM employees
GROUP BY department_id;ORA-00937: not a single-group group function
How to prevent it
When you write a group function, decide at once whether the result is one row in total or one row per group, and write the GROUP BY clause in the same step. A quick check before running: every column in the SELECT list is either inside a group function or listed in GROUP BY.
For running totals and "share of total" columns, reach for analytic functions (SUM(...) OVER (...)) rather than mixing aggregates with detail columns. Practise with counting orders per customer.
Common questions
How do I fix ORA-00937 not a single-group group function?
Add a GROUP BY clause that lists every column in the SELECT list that is not inside a group function, or remove those columns if you want one total row, or use an analytic function such as SUM(amount) OVER (PARTITION BY customer_id) to keep every row.
What is the difference between ORA-00937 and ORA-00979?
ORA-00937 is raised when a group function and a plain column are mixed and there is no GROUP BY at all (or a nested aggregate makes the query a single group). ORA-00979 is raised when there is a GROUP BY clause but a column used in SELECT, HAVING or ORDER BY is missing from it.
Why does the same query work in MySQL?
MySQL can accept a mix of aggregate and plain columns when its ONLY_FULL_GROUP_BY mode is off, and then returns a value from an arbitrary row. Oracle never does this: every plain column must be grouped.
Learn more on CodeWithSQL
Official documentation
- ORA-00937 (Oracle Database Error Messages)
- Aggregate Functions (Oracle SQL Language Reference 19c)
- SELECT (Oracle SQL Language Reference 19c)
Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.