What is the AVG operator in SQL and how is it used in the WHERE clause?

The AVG() operator in SQL is an aggregate function that calculates the average value of a set of values in a specified column.

AVG() cannot be used directly in a WHERE clause. The WHERE clause is used to filter rows before aggregate functions are applied.

To filter results based on the average value, you need to use the HAVING clause. HAVING is used to filter groups created with GROUP BY, after aggregate functions are applied.

Example of using AVG() with HAVING:

SELECT customer_id, AVG(order_amount) AS average_order_amount
FROM orders
GROUP BY customer_id
HAVING AVG(order_amount) > 100; -- Filtering by average value

This query selects the customer ID and the average order amount for each customer, then filters the results to only include customers whose average order amount exceeds 100.

If you need to use the average value in a condition without grouping data, you can use a subquery:

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products); -- Comparing with the overall average price

This query selects product names and prices where the price is above the average price of all products.