What is the purpose of HAVING and why not WHERE?

WHERE filters rows before grouping, that is, it applies to the raw data. HAVING, on the other hand, filters already grouped data, that is, the results of aggregate functions.

For example, if you want to select clients whose total orders exceed 1000, you cannot use WHERE because the sum is an aggregate function, and it is calculated after grouping:

SELECT customer_id, SUM(order_amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;

Here, HAVING filters groups based on the sum condition, whereas WHERE cannot do this because aggregates are not yet calculated at this stage.