Oracle Error ORA-00979

Error code

ORA-00979

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (wording differs: see below)

Category

SQL Syntax & Query Structure

Last reviewed

24 October 2026

What it means

ORA-00979 means the query has a GROUP BY clause, but the SELECT list, HAVING clause or ORDER BY clause uses an expression that is neither in the GROUP BY nor inside an aggregate function.

After GROUP BY, each group becomes one row, so only the grouped expressions, aggregates over the group and constants still have a single value. Oracle's current reference explains that the SELECT, HAVING and ORDER BY clauses are processed after GROUP BY: an expression that is not part of the grouped result can no longer be referenced there. The current message names the expression; 19c and 21c print only "not a GROUP BY expression", so you have to find it yourself.

The match is on the expression, not just the column: the 19c reference says the expression must match one of the expressions in the GROUP BY clause. Grouping by TRUNC(order_date) and selecting order_date fails, for example. If the query has no GROUP BY clause at all, Oracle raises ORA-00937 instead.

Common causes

• A column was added to the SELECT list of a grouped query but not to the GROUP BY clause.
• ORDER BY or HAVING uses a column that is not grouped, such as ORDER BY order_date in a query grouped by customer_id.
• The GROUP BY uses an expression (TRUNC(order_date), UPPER(name), EXTRACT(YEAR FROM ...)) but the SELECT list uses the bare column or a different expression.
• A query joins a lookup table and selects its descriptive column (for example c.name) while grouping only by the key c.customer_id.
• A scalar subquery in the SELECT list of a grouped query refers to a column of the outer query that is not grouped.

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.

SELECT o.customer_id, COUNT(DISTINCT c.name) AS names_per_group
FROM shop.orders o
JOIN shop.customers c ON c.customer_id = o.customer_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT c.name) > 1;

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.

SELECT o.customer_id, c.name, COUNT(*) AS order_count
FROM shop.orders o
JOIN shop.customers c ON c.customer_id = o.customer_id
GROUP BY o.customer_id, c.name;

SELECT customer_id,
       COUNT(*)        AS order_count,
       MAX(order_date) AS last_order,
       LISTAGG(status, ', ') WITHIN GROUP (ORDER BY status) AS statuses
FROM shop.orders
GROUP BY customer_id;

SELECT TRUNC(order_date, 'MM') AS order_month, COUNT(*) AS order_count
FROM shop.orders
GROUP BY TRUNC(order_date, 'MM')
ORDER BY TRUNC(order_date, 'MM');

SELECT customer_id, COUNT(*) AS order_count
FROM shop.orders
GROUP BY customer_id
ORDER BY MAX(order_date) DESC;

Examples

Selecting a column that is not grouped

Not reproduced here: no Oracle Database is installed for this site. The result follows the message format in Oracle's current error reference with this example's column filled in; how the expression is printed (case, quoting, table alias) is illustrative. Oracle Database 19c and 21c print "ORA-00979: not a GROUP BY expression".

SELECT customer_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

ORA-00979: STATUS: must appear in the GROUP BY clause or be used in an aggregate function

Grouping by a truncated date but selecting the raw date

Not reproduced here: no Oracle Database is installed for this site. The message format comes from Oracle's current error reference; the printed expression is illustrative, and 19c and 21c show "not a GROUP BY expression". Selecting TRUNC(order_date) instead matches the GROUP BY expression.

SELECT order_date, COUNT(*) AS order_count
FROM orders
GROUP BY TRUNC(order_date);

ORA-00979: ORDER_DATE: must appear in the GROUP BY clause or be used in an aggregate function

ORDER BY a column that the groups no longer have

Not reproduced here: no Oracle Database is installed for this site. The message format comes from Oracle's current error reference, which lists ORDER BY among the clauses processed after GROUP BY; the printed expression is illustrative. Use ORDER BY MAX(order_date) or another aggregate.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_date;

ORA-00979: ORDER_DATE: must appear in the GROUP BY clause or be used in an aggregate function

How to prevent it

Before running a grouped query, check each non-aggregated expression in SELECT, HAVING and ORDER BY against the GROUP BY list, written the same way. When the grouping key is a calculation, group in a subquery or a WITH clause, give it an alias there, and use the alias in the outer query.

Group by the key and pick descriptive columns deliberately: either group by them too (after checking they are one value per key) or wrap them in MIN, MAX or LISTAGG. See ORDER BY, GROUP BY and HAVING explained.

Common questions

Learn more on CodeWithSQL

• ORDER BY, GROUP BY and HAVING explained
• SQL aggregate functions explained

Official documentation

• ORA-00979 (Oracle Database Error Messages)
• SELECT, including the GROUP BY clause (Oracle SQL Language Reference 19c)
• LISTAGG (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.