A select statement cannot include both a group by and an order by clause.

Error ORA-00937 occurs when the GROUP BY command is improperly used. The GROUP BY command allows the user to view rows having a common field value in a single row. For example, a small business CEO wants to view employees who worked the highest number of hours by department last month. Another instance could be when a school superintendent wants to view students on the honor roll by homeroom. In such cases, the user may run a GROUP BY statement to view the results.

GROUP BY is used in conjunction with aggregate functions to filter the results by a value. The GROUP BY command can be very useful for viewing a select set of results. It is commonly used, which deems it essential to utilize the proper syntax when running the statement.      

The Problem

When faced with ORA-00923, the error message you will see is

ORA-00923: not a single-group group function

Oracle documentation states the cause as the following:

A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

In other words, you tried to execute a SELECT statement that requires a GROUP BY clause without including the GROUP BY clause. If you are using an aggregate function in your select query (e.g. AVG, COUNT, MAX, MIN…), you must have a GROUP BY clause.

The Solution

To resolve the error, you can either remove the group function or column expression from the SELECT clause or you can add a GROUP BY clause that includes the column expressions.

If you choose to add the GROUP BY clause, make sure to include the column expressions and follow the correct order. Take the example of the small business CEO who wants to view a list of employees who worked the most number of hours, organized by department. The correct syntax that includes the GROUP BY clause would be

SELECT department, MAX(hours) AS “most hours”

FROM employees

GROUP BY department;

Looking Forward 

Remember, if you are using an aggregate function in your select query then you must also have a GROUP BY clause. You cannot refer to a nonaggregated column in SELECT that is not also named in the GROUP BY clause. For the query to run successfully you must either remove the group function or column expression from SELECT or you must add a GROUP BY clause that includes the column expression.

Following this rule and ensuring proper query syntax should prevent error ORA-00937 from occurring in the future. Even though the process of correcting this error is not too difficult, contact your database administrator or licensed Oracle consultant if you continue to face problems with ORA-00937.

Are you making these errors with GROUP BY in SQL? Find out what they are, how to avoid them, and how to fix them.

SQL’s

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Stop Making These 7 Common GROUP BY Mistakes

1. Forgetting GROUP BY with Aggregate Functions

You use

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

The most common

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Here is one example. Imagine that you have the table recipes, which contains 100 records and six columns. This table stores the number of views (

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

recipe table

Here is a short description of the table’s columns:

• SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
  WHERE count(*) > 10 
  GROUP BY meal_category ;
  

  6 – The recipe category (soup, drinks, desserts, etc.).

• SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
  WHERE count(*) > 10 
  GROUP BY meal_category ;
  

  7 – The recipe’s name.

• SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
  WHERE count(*) > 10 
  GROUP BY meal_category ;
  

  8 – The author’s name.

• SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
  WHERE count(*) > 10 
  GROUP BY meal_category ;
  

  5 – The number of views (total pages/recipes viewed) in the current month.

• SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
             GROUP BY meal_category
  HAVING count(*) > 10  ;
  

  0 – The number of views (total pages/recipes viewed) in the previous month.

• SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
             GROUP BY meal_category
  HAVING count(*) > 10  ;
  

  1 – The author’s unique ID number.

Let's say that you want to count the number of recipes in each meal category. If you write the statement like this (without

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes ;

... most SQL engines will give you an error. Some engines, however, will output weird, unwanted results. I’m using MySQL and when I run this statement, I get this:

Result without GROUP BY

100 is the total count of all the recipes in the whole data set and the meal category ‘Cold appetizer’ is just one category out of ten. To correct this type of error, you need to add a

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

The correct SELECT looks like this:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Here is a short explanation of what’s going on:

• Records are merged based on meal category. For example, desserts are one group, soups another, main dishes yet another, etc. The column

  SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
  WHERE count(*) > 10 
  GROUP BY meal_category ;
  

  6 is specified after

  SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
  GROUP BY meal_category ;
  

  7; it is also listed in

  SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
  WHERE count(*) > 10 
  GROUP BY meal_category ;
  

  1.
• For each group, we are using

  SELECT 
    meal_category,
    count(*) AS total_recipes 
  FROM recipes
             GROUP BY meal_category
  HAVING count(*) > 10  ;
  

  7 to count the total number of recipes in that group.

I'm not going to dive deeply into the syntax here, but I would definitely suggest you read GROUP BY in SQL Explained or Using GROUP BY in SQL for more details.

As you can see, the result is like we expected:

Valid GROUP BY result

2. Confusing WHERE and HAVING

Maybe you’d like to see only those meal categories that have more than 10 recipes. A  lot of beginners would write this query:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

This statement will return an error because you cannot use aggregate functions in a

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

In our example, we want to filter rows after grouping; in cases like this, we need to use the

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

This misunderstanding about the difference between

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Let's clarify this difference with two more examples.

Example 1 -  How to Display Meal Categories with 1M+ Views 

A statement that displays only categories with more than 1 million total page views can be written like this:

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

Here we are using

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

Example with HAVING

Example 2 – John’s Performance in Each Meal Category

This query extracts only John's recipes and calculates his performance:

SELECT 
  meal_category, 
  sum(no_of_views) AS total 
FROM recipes 
WHERE author = ‘John’ 
GROUP BY meal_category;

We’re using WHERE because we need to filter records (so we only get John’s data) before we put the records into groups by meal category. Here is how the result looks:

John’s KPIs

SELECT 
  meal_category,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category
HAVING sum(no_of_views) >1000000;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

3. Listing a Column Inside SELECT but Not in GROUP BY

Now suppose you want to see the total number of views per

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

Does this look okay to you?  Of course not; it will throw an error in most SQL engines. For example, Oracle will tell you. Why this confusing error? What is missing here?

Well, the SQL engine doesn't know how to calculate the total for each author because we didn't include it in the GROUP BY clause; the attribute author is not listed inside the GROUP BY clause. This is another common error with GROUP BY.

Let's fix this query and run it one more time:

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

The result is:

Now this looks okay. Remember, unaggregated columns that are listed in

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

You don't list columns that are inside aggregate functions in

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
WHERE count(*) > 10 
GROUP BY meal_category ;

SELECT 
  meal_category, 
  sum(no_of_views) AS total 
FROM recipes 
WHERE author = ‘John’ 
GROUP BY meal_category;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

 If you would like to learn more about this topic, check out our article GROUP BY Clause: How Well Do You Know It?. It explains why SELECTed columns need to appear in the GROUP BY clause. Also, How to Fix a ‘Not a GROUP BY Expression’ Error gives more examples related to this type of error.

4. Not Grouping by a Unique Key

Now let’s try something else. Suppose we want to get the average number of page views for each recipe author. The following query calculates the average total number of page views for each author using the author name:

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

When you look at the result, you will notice that Lisa averages 116101.5 page views:

GROUP BY author – but names are not unique

However, we actually have two authors named Lisa in our table. When we group the results by the author column, both Lisas are averaged together. Why? Because we are using a non-unique column in the

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

Now we see how recipes from Lisa (id=11) are much more viewed than recipes by Lisa (id=5):

GROUP BY with author and author_id

It is important to always think about grouping keys. Grouping values should be unique and must represent each group in the desired way. Otherwise, you’ll get inaccurate, confusing results and possibly a GROUP BY error.

5. Confusing COUNT(distinct) and COUNT(*)

If you’re curious to see the total number of authors for each meal category, you can write a

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Here’s the result – but it’s not what you expected, is it?

This is the total number of recipes in each category, not the total number of authors. Why is that? Well,

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

One author can have many recipes in each category, so to get the information you want, you must count distinct authors (using

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

So, when should you use

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

Let's take a look at an example:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

The difference between

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

In the above example,

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

So, if you would like to display the total number of distinct authors per each meal category, use

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

For a more detailed explanation, see What is the Difference Between COUNT(*), COUNT(1), COUNT(column name), and COUNT(DISTINCT column name)?

6. Problems Using Aggregate Functions With NULLs

This is another ‘missing value’ problem. Let's say that you want to calculate the average total number of views from the previous month for each category. Your colleague calculated those figures, but they’d like you to double check the result.

Here is your query:

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

And what you get is ...

The result looks okay and you are confident when it comes to the correctness of your query. However, your colleague got slightly different figures:

What just happened? Why the different results?

 In a nutshell, the differing results arise from different interpretations of missing values.

The column

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

For example, Ally’s Banana Cheesecake recipe was written in the current month, so there are no statistics for the previous month:

Banana cheesecake was published in current month

Now, let's get back to those averages and their different results. Averages are calculated as the total sum of

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Notice how the averages from these two queries treat NULLs differently. For example, the ‘desserts’ category contains NULLs. Thus, the first query omits these rows and does not count them towards the total number of rows; this gives the value 144349.72. The second query replaces all NULLs with zero and counts these rows in the average, giving a smaller value  of 129914.75.

I would say that both queries could be valid, depending on how you want to calculate averages.

7. Using COUNT(*) with GROUP BY and a LEFT JOIN

Using

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

Let’s suppose that someone in marketing has the following table,

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

recipes_campaign

In addition to the data in

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

SELECT 
  author, author_id
  avg(no_of_views) 
FROM recipes 
GROUP BY author, author_id;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Here’s the result:

This isn’t what we expected. The table

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category;

How can we fix this? If we use

SELECT 
  meal_category,
  author,
  sum(no_of_views) AS total 
FROM recipes 
GROUP BY meal_category, author;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

Once you run this, you get:

Here,

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

SELECT 
  author,
  avg(no_of_views) 
FROM recipes 
GROUP BY author;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
           GROUP BY meal_category
HAVING count(*) > 10  ;

You Can Solve GROUP BY Errors!

Through several examples, we’ve explored GROUP BY and the most common errors that beginners often make. I hope that now you have a better sense of how

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

SELECT 
  meal_category,
  count(*) AS total_recipes 
FROM recipes
GROUP BY meal_category ;

When a SELECT statement includes WHERE HAVING and GROUP BY clauses the GROUP BY clause is always evaluated first?

What is true for GROUP BY order by clause?

Which clause can be used to restrict or filter the groups returned by a query based on a group function?

Which line of the SELECT statement is used to group data stored in the database?