HAVING in PostgreSQL 🚀

The HAVING clause is used to filter grouped results in PostgreSQL. It works with GROUP BY and is similar to WHERE, but HAVING is used for aggregated values.

1. Basic Syntax

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

• WHERE filters before aggregation.
• HAVING filters after aggregation.

2. Example: Filter Groups Based on Count

SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department HAVING COUNT(*) > 10;

🔹 Returns departments with more than 10 employees.

3. Example: Filter Groups Based on Average

SELECT category, AVG(price) AS avg_price FROM products GROUP BY category HAVING AVG(price) > 100;

🔹 Returns categories where the average price is greater than 100.

4. Example: Using HAVING with Multiple Conditions

SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id HAVING SUM(amount) > 500 AND COUNT(*) > 3;

🔹 Returns customers who have spent more than $500 and made more than 3 orders.

5. Example: Using HAVING with MIN and MAX

SELECT category, MAX(price) AS max_price FROM products GROUP BY category HAVING MAX(price) < 500;

🔹 Returns categories where the most expensive product is under $500.

6. Example: Using HAVING with DISTINCT

SELECT department, COUNT(DISTINCT role) AS unique_roles FROM employees GROUP BY department HAVING COUNT(DISTINCT role) > 2;

🔹 Returns departments with more than 2 unique roles.

7. Difference Between HAVING and WHERE

Example Comparison

-- Using WHERE (Before Aggregation)SELECT * FROM orders WHERE amount > 100;

🔹 Filters individual rows before aggregation.

-- Using HAVING (After Aggregation)SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING SUM(amount) > 100;

🔹 Filters grouped results after aggregation.

8. Example: Combining HAVING with ORDER BY

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

🔹 Returns top spenders, ordered by total spent.

9. Example: Using HAVING with Subqueries

SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department HAVING COUNT(*) > (SELECT AVG(employee_count) FROM (SELECT COUNT(*) AS employee_count FROM employees GROUP BY department) AS dept_counts);

🔹 Returns departments with more employees than the average department size.

10. Example: Using HAVING with Joins

SELECT u.name, COUNT(o.id) AS order_count FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name HAVING COUNT(o.id) > 5;

🔹 Returns users who placed more than 5 orders.

Performance Tip

• Use WHERE before aggregation for better performance.
• Index the columns used in GROUP BY for faster queries.

Next Steps

✅ Combine HAVING with JOIN and ORDER BY
✅ Optimize queries using indexes
✅ Use HAVING in Go Fiber queries