What is the difference between HAVING and WHERE conditions, and why are there restrictions for HAVING?

WHERE and HAVING are filtering conditions in SQL, but they are applied at different stages:

• WHERE filters rows before grouping. It limits the dataset that will be aggregated.
• HAVING filters already grouped data, i.e., the results of aggregate functions.

For example, to select customers with a total order amount greater than 1000:

SELECT customer_id, SUM(amount) as total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Restrictions for HAVING are related to the fact that it works with aggregates, so columns that are not in GROUP BY or not aggregated cannot be used in HAVING. In WHERE, aggregate functions cannot be used because filtering occurs before grouping.