ORA-00979 not a group by expression

Demystifying ORA-00979: Not a GROUP BY Expression in Oracle SQL

Understand and resolve the common Oracle error ORA-00979, which occurs when aggregate functions are used incorrectly with GROUP BY clauses.

The ORA-00979: not a GROUP BY expression error is one of the most frequently encountered issues when writing SQL queries involving aggregate functions in Oracle databases. It signals that your query violates a fundamental rule of SQL aggregation: any non-aggregated column in the SELECT list of a query that uses a GROUP BY clause must also be included in the GROUP BY clause itself. This article will break down the cause of this error, provide clear examples, and offer practical solutions to help you write correct and efficient aggregate queries.

Understanding the GROUP BY Clause

The GROUP BY clause is used to group rows that have the same values in specified columns into a set of summary rows. For each group, you can then apply aggregate functions (like SUM(), COUNT(), AVG(), MAX(), MIN()) to calculate summary information. The core principle is that for each group, the database needs to know which non-aggregated columns to display. If a column is not part of an aggregate function, it must be part of the GROUP BY clause to ensure a single, consistent value can be returned for each group.

Consider a table named EMPLOYEES with columns department_id, employee_name, and salary. If you want to find the total salary per department_id, you would group by department_id and sum salary.

SELECT department_id, SUM(salary)
FROM EMPLOYEES
GROUP BY department_id;

A correct query grouping by department_id and summing salaries.

The Cause of ORA-00979

The ORA-00979 error occurs when you include a non-aggregated column in your SELECT list that is not present in your GROUP BY clause. Oracle cannot determine which value to display for that non-grouped column because there might be multiple distinct values for it within a single group. The database needs a deterministic way to present the data, and by omitting a column from GROUP BY, you're asking it to pick one value out of potentially many, which it won't do without explicit instruction.

SELECT department_id, employee_name, SUM(salary)
FROM EMPLOYEES
GROUP BY department_id;

This query will raise ORA-00979 because employee_name is not in GROUP BY.

Resolving ORA-00979

There are generally three main approaches to resolve the ORA-00979 error, depending on your desired output:

Solution 1: Add the Column to GROUP BY

If you need to display the non-aggregated column and it makes sense to further group your results by it, simply add it to the GROUP BY clause. This will create more granular groups.

SELECT department_id, employee_name, SUM(salary)
FROM EMPLOYEES
GROUP BY department_id, employee_name;

Corrected query by adding employee_name to the GROUP BY clause.

Solution 2: Apply an Aggregate Function to the Column

If you don't want to group by the problematic column but still need to display some value for it (e.g., the first, last, min, or max value within each group), you can apply an aggregate function to it. This tells Oracle how to pick a single value from the multiple possibilities within each group.

SELECT department_id, MIN(employee_name) AS first_employee_in_dept, SUM(salary)
FROM EMPLOYEES
GROUP BY department_id;

Using MIN() on employee_name to pick one name per department.

Solution 3: Use Subqueries or Common Table Expressions (CTEs)

For more complex scenarios, especially when you need to retrieve other details alongside aggregated data, using subqueries or CTEs can be very effective. This allows you to first aggregate your data and then join it back to the original table (or another subquery) to retrieve additional non-aggregated columns.

Flowchart for resolving ORA-00979 using subqueries.

WITH DepartmentSalaries AS (
    SELECT department_id, SUM(salary) AS total_salary
    FROM EMPLOYEES
    GROUP BY department_id
)
SELECT e.employee_name, e.department_id, ds.total_salary
FROM EMPLOYEES e
JOIN DepartmentSalaries ds ON e.department_id = ds.department_id;

Using a CTE to first aggregate and then join back for employee details.

Practical Steps to Debug and Fix ORA-00979

When you encounter ORA-00979, follow these steps to efficiently identify and resolve the issue:

1. Step 1

Identify all non-aggregated columns in your SELECT list: Go through your SELECT clause and list every column that is not wrapped in an aggregate function (e.g., SUM(), COUNT(), AVG()).

2. Step 2

Examine your GROUP BY clause: Compare the list from step 1 with the columns present in your GROUP BY clause.

3. Step 3

Find the discrepancy: Any column from step 1 that is not in step 2 is the cause of the ORA-00979 error.

4. Step 4

Choose a resolution strategy: Decide whether you need to add the column to GROUP BY (Solution 1), apply an aggregate function to it (Solution 2), or restructure your query with subqueries/CTEs (Solution 3).