Oracle ORA-00979 Error: Causes and Solutions Complete Guide

ORA-00979: not a GROUP BY expression

ORA-00979 is one of the most common SQL errors in Oracle, occurring when a column or expression in the SELECT clause is neither wrapped in an aggregate function nor listed in the GROUP BY clause. Oracle enforces strict grouping rules: every non-aggregated item in SELECT must appear in GROUP BY. This error is straightforward to fix once you understand the root cause.

---

Top 3 Causes and Fixes

Cause 1: Missing Column in GROUP BY

The most frequent cause — you select multiple columns but forget to add all of them to GROUP BY.

Broken query:

-- ORA-00979 error
SELECT department_id, department_name, SUM(salary)
FROM employees
GROUP BY department_id;
-- department_name is in SELECT but missing from GROUP BY

Fixed query:

-- All non-aggregated columns must appear in GROUP BY
SELECT department_id, department_name, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id, department_name;

---

Cause 2: Expression in SELECT Doesn't Match GROUP BY Exactly

When you use a function or expression in SELECT, the exact same expression must appear in GROUP BY — not the original column name.

Broken query:

-- ORA-00979 error
SELECT SUBSTR(hire_date, 1, 4) AS hire_year,
       COUNT(*) AS emp_count
FROM employees
GROUP BY hire_date;
-- hire_date ≠ SUBSTR(hire_date, 1, 4)

Fixed query:

-- Use the same expression in GROUP BY
SELECT SUBSTR(hire_date, 1, 4) AS hire_year,
       COUNT(*) AS emp_count
FROM employees
GROUP BY SUBSTR(hire_date, 1, 4);

---

Cause 3: Using a Column Alias in GROUP BY

Oracle does not allow SELECT aliases to be referenced in the GROUP BY clause of the same query level. Developers familiar with MySQL sometimes make this mistake.

Broken query:

-- ORA-00979 error
SELECT TRUNC(order_date, 'MM') AS order_month,
       SUM(amount)             AS total_amount
FROM orders
GROUP BY order_month;
-- 'order_month' alias is not valid in GROUP BY in Oracle

Fixed query:

-- Repeat the full expression in GROUP BY
SELECT TRUNC(order_date, 'MM') AS order_month,
       SUM(amount)             AS total_amount
FROM orders
GROUP BY TRUNC(order_date, 'MM');

Note: Aliases are allowed in ORDER BY in Oracle, but not in GROUP BY or WHERE.

---

Quick Fix Checklist

Run through this mental checklist every time you write a GROUP BY query:

SELECT
    col_a,           -- non-aggregate → MUST be in GROUP BY
    col_b,           -- non-aggregate → MUST be in GROUP BY
    FUNC(col_c),     -- non-aggregate expression → MUST be in GROUP BY as FUNC(col_c)
    SUM(col_d),      -- aggregate → does NOT need GROUP BY
    COUNT(col_e)     -- aggregate → does NOT need GROUP BY
FROM your_table
GROUP BY
    col_a,
    col_b,
    FUNC(col_c);     -- must exactly match the SELECT expression

---

Prevention Tips

1. Write GROUP BY immediately after SELECT
As soon as you add a non-aggregated column to SELECT, add it to GROUP BY right away. Don't wait until the query is complete — this is when omissions happen.

2. Use analytic functions as an alternative
For complex reports where maintaining GROUP BY becomes unwieldy, consider using analytic (window) functions like SUM() OVER(). They let you aggregate without grouping rows, eliminating ORA-00979 entirely in those scenarios:

-- No GROUP BY needed with analytic functions
SELECT employee_id,
       department_id,
       salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;

---

Related Errors

Error Code	Description
ORA-00937	not a single-group group function — mixing aggregate and non-aggregate without GROUP BY
ORA-00934	group function is not allowed here — aggregate used in WHERE instead of HAVING
ORA-00935	group function is nested too deeply — more than 2 levels of aggregate nesting

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.