Oracle: ORA-00934 Error Message

ORA-00934 Error Message

Learn the cause and how to resolve the ORA-00934 error message in Oracle.

Description

When you encounter an ORA-00934 error, the following error message will appear:

• ORA-00934: group function is not allowed here

Cause

You tried to execute a SQL statement that included one of the group functions (ie: MIN Function, MAX Function, SUM Function, COUNT Function) in either the WHERE clause or the GROUP BY clause.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Try removing the group function from the WHERE clause or GROUP BY clause. If required, you can move the group function to the HAVING clause.

For example, if you tried to execute the following SQL statement:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
WHERE SUM(sales) > 1000
GROUP BY department;

You would receive the following error message:

You could correct this statement by using the HAVING clause as follows:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Option #2

You could also try moving the group by function to a SQL subquery.

For example, if you tried to execute the following SQL statement:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
WHERE SUM(sales) > 1000
GROUP BY department;

You would receive the following error message:

You could correct this statement by using a subquery as follows:

SELECT order_details.department,
SUM(order_details.sales) AS "Total sales"
FROM order_details, (SELECT department, SUM(sales) AS "Sales_compare"
                     FROM order_details
                     GROUP BY department) subquery1
WHERE order_details.department = subquery1.department
AND subquery1.Sales_compare > 1000
GROUP BY order_details.department;