ORA-00979: not a GROUP BY expression

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

SELECT or ORDER BY uses a column that is neither in GROUP BY nor inside an aggregate function.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-15

With GROUP BY, every row of the result is one group. Any column you show or sort by must be one of the grouping expressions or be wrapped in an aggregate such as SUM or MAX. Otherwise Oracle can't tell which row's value to use.

Older versions only said not a GROUP BY expression. 23ai names the column: ORA-00979: "JOB": must appear in the GROUP BY clause or be used in an aggregate function. Start from that column.

Causes and fixes

  1. A selected column is missing from GROUP BY
  2. ORDER BY on a column that isn't grouped
  3. GROUP BY uses a different expression than SELECT

1A selected column is missing from GROUP BY

SELECT dept, job, SUM(sal) ... GROUP BY dept failed on "JOB". Counting distinct jobs per department showed SALES has 2 (MGR and CLERK), so there is no single job value per department.

Adding the column to GROUP BY (GROUP BY dept, job) returned 3 rows: IT DEV 400, SALES CLERK 580, SALES MGR 500.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, dept VARCHAR2(10), job VARCHAR2(10), sal NUMBER);
INSERT INTO emp VALUES (1, 'SALES', 'MGR', 500);
INSERT INTO emp VALUES (2, 'SALES', 'CLERK', 300);
INSERT INTO emp VALUES (3, 'SALES', 'CLERK', 280);
INSERT INTO emp VALUES (4, 'IT', 'DEV', 400);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT dept, job, SUM(sal) AS total FROM emp GROUP BY dept;
OUTPUT
SELECT dept, job, SUM(sal) AS total FROM emp GROUP BY dept
             *
ERROR at line 1:
ORA-00979: "JOB": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
Checkrun as lab_a
SELECT dept, COUNT(DISTINCT job) AS job_cnt FROM emp GROUP BY dept ORDER BY dept;
OUTPUT
DEPT          JOB_CNT
---------- ----------
IT                  1
SALES               2

2 rows selected.

Adding a column changes the grouping (more rows). If you want one row per department, remove the column or aggregate it instead.

After the fixrun as lab_a
SELECT dept, job, SUM(sal) AS total FROM emp GROUP BY dept, job ORDER BY dept, job;
OUTPUT
DEPT       JOB             TOTAL
---------- ---------- ----------
IT         DEV               400
SALES      CLERK             580
SALES      MGR               500

3 rows selected.

2ORDER BY on a column that isn't grouped

The SELECT list was fine, but ORDER BY sal DESC failed on "SAL". SALES has two salaries (300 and 500); after grouping only the total is left, so there is no single sal to sort by.

Sorting by the aggregate (ORDER BY SUM(sal) DESC) worked: SALES 800, IT 400.

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, dept VARCHAR2(10), job VARCHAR2(10), sal NUMBER);
INSERT INTO emp VALUES (1, 'SALES', 'MGR', 500);
INSERT INTO emp VALUES (2, 'SALES', 'CLERK', 300);
INSERT INTO emp VALUES (3, 'IT', 'DEV', 400);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT dept, SUM(sal) AS total FROM emp GROUP BY dept ORDER BY sal DESC;
OUTPUT
SELECT dept, SUM(sal) AS total FROM emp GROUP BY dept ORDER BY sal DESC
                                                               *
ERROR at line 1:
ORA-00979: "SAL": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
Checkrun as lab_a
SELECT dept, sal FROM emp ORDER BY dept, sal;
OUTPUT
DEPT              SAL
---------- ----------
IT                400
SALES             300
SALES             500

3 rows selected.
After the fixrun as lab_a
SELECT dept, SUM(sal) AS total FROM emp GROUP BY dept ORDER BY SUM(sal) DESC;
OUTPUT
DEPT            TOTAL
---------- ----------
SALES             800
IT                400

2 rows selected.

3GROUP BY uses a different expression than SELECT

SELECT showed TO_CHAR(order_date, 'YYYY-MM') but GROUP BY used TO_CHAR(order_date, 'YYYY'), so Oracle reported "ORDER_DATE". Listing both values per order showed the 2026 group holds two months (2026-01, 2026-02). This often happens when a yearly report is changed to monthly in only one place.

Using exactly the same expression in GROUP BY gave 2026-01 300 and 2026-02 150.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, order_date DATE, amount NUMBER);
INSERT INTO orders VALUES (1, DATE '2026-01-05', 100);
INSERT INTO orders VALUES (2, DATE '2026-01-20', 200);
INSERT INTO orders VALUES (3, DATE '2026-02-03', 150);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT TO_CHAR(order_date, 'YYYY-MM') AS ym, SUM(amount) AS total
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY');
OUTPUT
SELECT TO_CHAR(order_date, 'YYYY-MM') AS ym, SUM(amount) AS total
               *
ERROR at line 1:
ORA-00979: "ORDER_DATE": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
Checkrun as lab_a
SELECT order_id, TO_CHAR(order_date, 'YYYY') AS y, TO_CHAR(order_date, 'YYYY-MM') AS ym FROM orders ORDER BY order_id;
OUTPUT
  ORDER_ID Y    YM
---------- ---- -------
         1 2026 2026-01
         2 2026 2026-01
         3 2026 2026-02

3 rows selected.
After the fixrun as lab_a
SELECT TO_CHAR(order_date, 'YYYY-MM') AS ym, SUM(amount) AS total
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY ym;
OUTPUT
YM           TOTAL
------- ----------
2026-01        300
2026-02        150

2 rows selected.

FAQ

What does ORA-00979 not a GROUP BY expression mean?

A column in SELECT, ORDER BY or HAVING is not in GROUP BY and not inside an aggregate function. 23ai includes the column name in the message.

How do I fix ORA-00979?

Add the column to GROUP BY, or wrap it in an aggregate (SUM, MAX, ...). For ORDER BY, sort by a grouped column or an aggregate such as ORDER BY SUM(sal) DESC.

Why ORA-00979 with TO_CHAR or CASE in the SELECT?

The expression in GROUP BY must match the one in SELECT. TO_CHAR(d, 'YYYY-MM') and TO_CHAR(d, 'YYYY') are different expressions.

Is ORA-00979 the same as ORA-00937?

No. ORA-00937 is when there is no GROUP BY at all but you mix an aggregate with a plain column. ORA-00979 is when GROUP BY exists but a column is missing from it.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors