Resolve ORA-00934 Error Message in Oracle

Learn the motive and how to unravel 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 assertion that included one of the group functions (ie: MIN Function, MAX Function, SUM Function, COUNT Function) in both the WHERE clause or the GROUP BY clause.

Resolution

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

Option #1

Try casting off the group feature from the WHERE clause or GROUP BY clause. If required, you can pass the group characteristic 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 ought to right this announcement by way of 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 should additionally attempt transferring the group by feature 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 ought to correct this statement by using 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;