Encountering the ORA-00979 not a group by expression error is a common rite of passage for many SQL developers working with Oracle databases. This specific SQL error message indicates a fundamental misunderstanding or misapplication of how aggregate functions interact with the GROUP BY clause. Far from being a cryptic warning, ORA-00979 serves as a crucial reminder of SQL’s strict rules regarding data aggregation. When you’re trying to summarize data using functions like SUM(), COUNT(), or AVG(), Oracle expects every non-aggregated column in your SELECT list to be part of the grouping criteria. This article will thoroughly explain the root causes of this error, provide clear examples, and offer practical, step-by-step solutions to help you write more robust and error-free SQL queries.

Understanding the GROUP BY Clause Fundamentals

The GROUP BY clause is an indispensable tool in SQL, designed to organize rows that share the same values into summary rows. This process allows you to perform aggregate functions on each group, yielding a single result for each distinct combination of grouped columns. For instance, if you want to calculate the total sales per region, you would group your sales data by the region column and then apply the SUM() function to the sales amount within each region.

The core principle behind GROUP BY is that for any row returned by your query, Oracle must be able to determine a single, unambiguous value for each column. When you use an aggregate function, such as COUNT() or MAX(column_name), you are telling the database to produce a single value for a set of rows. Therefore, any other columns you project in your SELECT statement that are not part of an aggregate function must be included in your GROUP BY clause. This ensures that for every unique group, there’s a corresponding, single value for those non-aggregated columns. Neglecting this rule is the primary reason for the ORA-00979 not a group by expression error.

According to the official [I couldn’t find any examples that had both GROUP BY and ORDER BY clauses in the same query. I tried removing each field from the group by one at a time, but am still getting the same error.

You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value (like MIN, MAX or SUM).

A simple example to understand why this happens: Imagine you have a database like this:

FOO BAR 0 A 0 B 

and you run SELECT * FROM table GROUP BY foo. This means the database must return a single row as result with the first column 0 to fulfill the GROUP BY but there are now two values of bar to chose from. Which result would you expect - A or B? Or should the database return more than one row, violating the contract of GROUP BY?](<https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlqr/SELECT.htmlGUID-A9C3386D-0797-4552-850C-1718D59 Question & Answer :

I am getting ORA-00979 with the following query:

SELECT cr.review_sk, cr.cs_sk, cr.full_name, tolist(to_char(cf.fact_date, ‘mm/dd/yyyy’)) >)