The HAVING clause is used in SQL to filter aggregated data. It works with GROUP BY to restrict the groups of rows returned, based on conditions on aggregate functions like COUNT(), SUM(), AVG(), etc.

---

✅ Syntax

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING condition;

---

📘 Difference Between WHERE and HAVING

Clause	Used For	Works On
WHERE	Filter rows before grouping	Raw table data
HAVING	Filter groups after aggregation	Grouped results

---

🎓 Example Table: orders

order_id	customer_id	amount
1	101	500
2	102	300
3	101	400
4	103	700
5	102	200

---

🔍 Query Example: Total Order Amount Per Customer

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

Result:

customer_id	total_spent
101	900
102	500
103	700

---

🚨 Apply HAVING: Customers Who Spent More Than 600

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 600;

Result:

customer_id	total_spent
101	900
103	700

---

🧠 Combine WHERE and HAVING

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE amount > 200
GROUP BY customer_id
HAVING COUNT(*) >= 2;

This filters out low-value orders first, then counts remaining orders per customer, and finally filters those with at least 2 such orders.

---

🛠 Use with Other Aggregate Functions

SELECT customer_id, AVG(amount) AS avg_order
FROM orders
GROUP BY customer_id
HAVING AVG(amount) >= 400;

---

📌 Tips and Best Practices

• Use HAVING only when dealing with aggregate results (SUM, AVG, etc.)
• Use WHERE to pre-filter data before aggregation
• You can alias aggregate functions (e.g., AS total) and use those in HAVING (but not in all databases — MySQL supports it)

---

🎯 Summary

Clause	Purpose	Example Use
WHERE	Filter rows	WHERE amount > 100
HAVING	Filter groups	HAVING SUM(amount) > 500