Oracle/PLSQL: ORA-00934 Error

Error:

Cause:

Action:

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;

2. 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;