What is the purpose of the HAVING clause in SQL?

The HAVING operator is used to filter groups created with the GROUP BY operator. Unlike the WHERE operator, which filters rows before grouping, HAVING filters groups after they are created. HAVING is typically used for filtering based on aggregate functions.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition // Filtering rows before grouping
GROUP BY column_name(s)
HAVING having_condition // Filtering groups after grouping
ORDER BY column_name(s);

Example:

Suppose there is a table orders with fields customer_id, order_date, amount. You want to find customers whose total order amount exceeds 1000.

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

In this example:

• GROUP BY customer_id groups orders by each customer.
• SUM(amount) calculates the total order amount for each group (each customer).
• HAVING SUM(amount) > 1000 filters groups, leaving only those customers whose total order amount is greater than 1000.

HAVING is necessary because the WHERE operator cannot directly filter the results of aggregate functions.