ORA-00979: not a GROUP BY expression

The SELECT list contains a column that is neither in GROUP BY nor inside an aggregate function. Add it to GROUP BY, or wrap it in an aggregate such as MAX.

What ORA-00979 means

A GROUP BY query returns one row per group, so every selected column must have one value per group. Columns in the GROUP BY list do; aggregates such as SUM and COUNT do. Anything else has many possible values, and Oracle refuses to pick one. With an aggregate and no GROUP BY at all, the error is ORA-00937 instead.

Common causes of ORA-00979

A selected column missing from GROUP BY.
An ORDER BY on a column that is not grouped.
An expression in SELECT that differs from the one in GROUP BY, such as TRUNC(d) against d.
A SELECT-list alias used in GROUP BY on releases before 23ai.

How to fix ORA-00979

Add every non-aggregated column to GROUP BY.
Or aggregate it: MAX(col) if any single value will do.
Repeat the same expression in SELECT and GROUP BY, character for character.
If you need detail next to totals, use an analytic function: SUM(x) OVER (PARTITION BY y).

ORA-00979 example

Every selected column must be grouped or aggregated:

-- ORA-00979: not a GROUP BY expression
SELECT city, name, COUNT(*) FROM customers GROUP BY city;

-- Works
SELECT city, COUNT(*) FROM customers GROUP BY city;

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00979. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.