Syntax confusion in SQL

Question

List all products (PRODUCT_ID, PRODUCT_NAME) and their remaining stock (QUANTITY_IN_STOCK - REORDER_POINT). List only those rows whose remaining stock is more than the average REORDER_QTY value.

So far we have:

SELECT product_name, product_id, quantity_in_stock - reorder_point
FROM product
WHERE quantity_in_stock - reorder_point > avg(reorder_qty)
GROUP BY product_name;

The problem we're having is with the avg(reorder_qty), but we did some Google searching and looked through our text. Any help would be appreciated. :)

I understand it's typically taboo to ask homework questions, but we're not asking for the answer, but an explanation of where we went wrong that would lead us to the answers ourselves. :)

Thanks!

Answer 1

You can't use aggregate functions like avg in a WHERE clause, since WHERE governs which rows go into the GROUP BY statement. You need to impose a condition on the output of the grouped data. The HAVING clause does this.