ORA-00979: not a GROUP BY expression
ORA-00979: "JOB": must appear in the GROUP BY clause or be used in an aggregate functionSELECT 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
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
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;Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT dept, job, SUM(sal) AS total FROM emp GROUP BY dept;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/SELECT dept, COUNT(DISTINCT job) AS job_cnt FROM emp GROUP BY dept ORDER BY dept;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.
SELECT dept, job, SUM(sal) AS total FROM emp GROUP BY dept, job ORDER BY dept, job;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
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;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT dept, SUM(sal) AS total FROM emp GROUP BY dept ORDER BY sal DESC;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/SELECT dept, sal FROM emp ORDER BY dept, sal;DEPT SAL
---------- ----------
IT 400
SALES 300
SALES 500
3 rows selected.SELECT dept, SUM(sal) AS total FROM emp GROUP BY dept ORDER BY SUM(sal) DESC;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
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;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT TO_CHAR(order_date, 'YYYY-MM') AS ym, SUM(amount) AS total
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY');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/SELECT order_id, TO_CHAR(order_date, 'YYYY') AS y, TO_CHAR(order_date, 'YYYY-MM') AS ym FROM orders ORDER BY order_id; ORDER_ID Y YM
---------- ---- -------
1 2026 2026-01
2 2026 2026-01
3 2026 2026-02
3 rows selected.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;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