How to Fix a 'Not a GROUP BY Expression' Error

Whether you are an experienced SQL user or only a beginner, your SQL queries may return errors. The accompanying messages are supposed to help you fix them; however, sometimes the messages are not very helpful. The Oracle message “Not a GROUP BY expression” is one such example. Let’s review what this is about and how to fix it.

We are going to assume some basic understanding of how GROUP BY works. If you are not familiar with grouping data in SQL, check out this introductory article on using GROUP BY in SQL or go to our interactive SQL Basics course where you'll learn the foundations of SQL, including GROUP BY.

If you are familiar with GROUP BY, you should recall the following general rule: each column in the SELECT statement should either be called in an aggregate function or be in the GROUP BY clause.

ORA-00979 “Not a GROUP BY expression” is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. This error message can be confusing to beginners.

Let’s review an example to understand why this error occurs. Consider the following table with basic information about each of our customers: ID, name, city, state, date of the last purchase, and the total amount of purchases.

Suppose we want to know the date of the last purchase and the average purchase amount per customer by state. We use the following query:

SELECT state, city, MAX(last_purchase_date) AS last_purchase,     
       AVG(purchases) AS avg_purchases
FROM customers
GROUP BY state;

If you try to run this query, the Oracle database will output the following error message:

ORA-00979: not a GROUP BY expression

From this, it is not obvious what you need to fix. Other databases provide more understandable messages:

• PostgreSQL. ERROR: column "customers.city" must appear in the GROUP BY clause or be used in an aggregate function
• SQL Server. Column 'customers.city' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So, what’s wrong with our query?

Why Does the Oracle Database Report This Error?

In the query above, we ask the database to show the following information about the customers:

• state,
• city,
• the last purchase date, and
• the total purchase amount.

Of these four columns, the state column appears in the GROUP BY clause, and the last_purchase_date and purchases columns are aggregated using MAX() and AVG() functions, respectively. The city column is neither called by an aggregate function nor is listed in GROUP BY. However, we have two cities in the state of California (“CA”) and two cities in the state of New York (“NY”). The database simply doesn’t know which value to display.

If you want to learn more about how GROUP BY works, check out my previous article, where I show how rows are grouped in SQL.

How to Fix the “Not a GROUP BY Expression” Error

Your approach to fixing this error will depend on what you want to achieve. Here are the options to consider.

Option 1. Add the city column to GROUP BY. If you want the city to be displayed in the output, you’ll need to group the data by both state and city.

SELECT state, city, MAX(last_purchase_date) AS last_purchase,
       AVG(purchases) AS avg_purchases
FROM customers
GROUP BY state, city;

Here is the result you’ll get when grouping by state and city:

Option 2. Remove the city column from SELECT. If you want your output to be grouped by state only, you’ll need to remove city from the SELECT statement. As I have demonstrated above, it is simply not possible to display the city when the rows are grouped only by state.

SELECT state, MAX(last_purchase_date) AS last_purchase,
       AVG(purchases) AS avg_purchases
FROM customers
GROUP BY state;

The result will be as follows:

Option 3. Call the city column in an aggregate function. In some cases, you may want to aggregate data in the column using a function such as COUNT(), SUM(), AVG(), MAX(), or MIN().

In our example, we may choose to display how many unique cities with customers there are in each state:

SELECT state, count(DISTINCT city) AS cities_with_customers, 
       MAX(last_purchase_date) AS last_purchase,
       AVG(purchases) AS avg_purchases
FROM customers
GROUP BY state;

Here’s what you’ll get when you run this query:

Check out this article for more examples of GROUP BY.

Fix the “Not a GROUP BY Expression” Error and Practice More GROUP BY!

You’ve learned why the error “Not a GROUP BY expression” occurs and how to fix it. It’s time to put this knowledge into practice with some real-world examples. I recommend starting with these interactive courses on LearnSQL.com:

• SQL Basics is an introductory SQL course that covers all basic topics, including grouping and aggregating data in SQL.
• SQL Practice Set is a set of over 80 SQL exercises to help you practice grouping and more in SQL.
• Creating Basic SQL Reports focuses on the details of GROUP BY not covered in the SQL Basics course. Here, you will learn about some common mistakes with GROUP BY and see how it is used in real-world reports.

Happy learning!