Back to articles list October 27, 2020 - 5 minutes read How to Fix a 'Not a GROUP BY Expression' Error Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql GROUP BY 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. 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. idnamecitystatelast_purchase_datepurchases 1Peter WhiteSan FranciscoCA2020-09-09120.85 2Helen BrownSan FranciscoCA2019-04-1124.85 3Jeff GreyLos AngelesCA2020-03-231085.00 4Kristine BlackNew YorkNY2020-10-02230.50 5Donald PageNew YorkNY2020-06-302345.00 6Robert LeeLos AngelesCA2019-12-0611.00 7Patrick CollinsSan FranciscoCA2020-02-12200.30 8Kate NordBuffaloNY2020-05-25435.00 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. statecitylast_purchaseavg_purchases CASan Francisco ORLos Angeles?MAX(last_purchase_date)AVG(purchases) NYNew YorkORBuffalo?MAX(last_purchase_date)AVG(purchases) 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: statecitylast_purchaseavg_purchases NYBuffalo2020-05-25435.00 CASan Francisco2020-09-09115.33 CALos Angeles2020-03-23548.00 NYNew York2020-10-021287.75 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: statelast_purchaseavg_purchases CA2020-09-09288.40 NY2020-10-021003.50 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: statecities_with_customerslast_purchaseavg_purchases CA22020-09-09288.40 NY22020-10-021003.50 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. 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. Finally, now that you’re firmly grounded in the key rule of the GROUP BY clause – namely, all non-aggregated columns from the SELECT statement should be in GROUP BY – it turns out that this is not always the case! Intrigued? Read this article to learn more. Happy learning! Tags: sql learn sql GROUP BY You may also like Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more GROUP BY in SQL Explained Need to refresh your knowledge of SQL GROUP BY? Learn how GROUP BY works and when it can be useful. Examples provided. Read more Using GROUP BY in SQL Master the powerful SQL GROUP BY command. Learn to compute statistical summaries with aggregate functions to solve real-world Business Intelligence challenges. Read more GROUP BY Clause: How Well Do You Know It? The GROUP BY clause is the most basic way to compute statistics in SQL. It can be quite tough for beginners but it is really powerful. Read more SQL Window Functions vs. GROUP BY: What’s the Difference? Window functions and GROUP BY may seem similar at first, but they’re quite different. Learn how window functions differ from GROUP BY and aggregate functions. Read more NULL Values and the GROUP BY Clause We've already covered how to use the GROUP BY clause but how does SQL's GROUP BY clause work when NULL values are involved? Find out! Read more Getting the Hang of the GROUP BY Clause GROUP BY is an important part of the SQL SELECT statement. But new SQL coders can run into some problems when this clause is used incorrectly. Here’s how to avoid those issues. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.