Oracle/PLSQL: ORA-00934 Error

Error:

ORA-00934: group function is not allowed here

Cause:

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

Action:

The options to resolve this Oracle error are:

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;

You could also try moving the group by function to a 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;