ORA-00937: not a single-group group function MAX

ORA-00937

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.