ORA-00979: not a GROUP BY expression error occurs when the Oracle SQL GROUP BY clause does not contain all of the expressions specified in the SELECT clause of the SQL. The GROUP BY clause must be specified for any SELECT expression that is not part of the GROUP function. The Oracle SQL SELECT statement should be modified to include the expression or column listed in the SELECT list in the GROUP BY clause. In the SELECT list, any expression or column list that is not specified in the GROUP BY should be removed. The error ORA-00979: not a GROUP BY expression will be resolved.
When the GROUP BY clause is used in a SQL query, the database must return a single row with the column names specified in the GROUP BY clause as a result. If some of the SELECT LIST column names are missing from the GROUP BY clause, the select query returns more than one row for the grouped columns. This is a breach of the GROUP BY contract. When using GROUP BY to group based on a list of columns, the sql query should return unique values from the column list. If some of the columns in the GROUP BY are omitted, duplicate rows will be returned. As a result, the error will be thrown.
The Problem
If an aggregation function is used in the Oracle SQL query, the GROUP BY clause should be used to apply the aggregation function. The columns list that must be used to group should be specified in the GROUP BY clause. If some of the SELECT LIST column names are not specified in the GROUP BY clause, the query will return more than one row for the grouped columns. In this case, the error will be thrown.
select d.department_id, d.department_name, count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id;Error
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:Solution 1
Columns other than aggregated columns in the SELECT LIST should be added to the GROUP BY clause’s columns list. If any of the columns are missing from the GROUP BY clause, include them in the GROUP BY clause. This ensures that the query returns only the unique rows for the added columns in the GROUP BY clause. The error can be fixed by including the missing columns in the GROUP BY clause.
Error
select d.department_id, d.department_name, count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id;Solution
select d.department_id, d.department_name, count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id, d.department_name;Solution 2
Remove the columns from the GROUP BY clause if you have added columns that are optional or not required in the SELECT LIST. This will make it easier to list the required columns in the sql query, and the group by query will run faster. The error can be fixed by removing the unnecessary columns from the SELECT LIST.
Error
select d.department_id, count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id;Solution
select d.department_id, d.department_name, count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id;Solution 3
If you want to list a column that isn’t required to be grouped with the GROUP BY clause, use the appropriate aggregation function. The columns list in the SELECT list must be added in either the GROUP BY clause or the aggregation function. Otherwise, an error will be thrown by the Oracle.
Error
select d.department_id, count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id;Solution
select d.department_id, max(d.department_name), count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id;Solution 4
If you use a function other than the aggregation function, include the non-aggregated function in the GROUP BY clause. The non-aggregated function may result in more than one row being returned. The query will throw an error if it returns duplicate rows. In the example below can return multiple rows based on case sensitive data.
Error
select UPPER(d.department_name), count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_name;Solution
select UPPER(d.department_name), count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by UPPER(d.department_name);Solution 5
Remove the GROUP BY clause and the aggregation functions if you don’t want to use the aggregation function and are only interested in unique rows from the sql query. The distinct keyword can be used to return the query’s unique values. This will resolve the error and make the query return faster.
Error
select d.department_id, d.department_name, count(e.employee_id)
from employees e
join departments d on d.department_id=e.department_id
group by d.department_id;Solution
select distinct d.department_id, d.department_name
from employees e
join departments d on d.department_id=e.department_id;
OR
select distinct department_id, department_name from departments;