PostgreSQL HAVING Clause

The HAVING clause in PostgreSQL is used to filter groups created by the GROUP BY clause. Unlike the WHERE clause, which filters individual rows before aggregation,HAVING filters aggregated results. It is commonly used with aggregate functions likeCOUNT, SUM, AVG, MIN, and MAX.

1. Basic Syntax

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

- condition usually involves aggregate functions - Filters groups rather than individual rows

2. Example: Filtering Groups with COUNT

-- Departments with more than 5 employees
            SELECT department, COUNT(*) AS total_employees
            FROM employees
            GROUP BY department
            HAVING COUNT(*) > 5;

3. Using HAVING with SUM, AVG, MIN, MAX

-- Departments with total salary > 200000
            SELECT department, SUM(salary) AS total_salary
            FROM employees
            GROUP BY department
            HAVING SUM(salary) > 200000;

            -- Departments with average salary >= 50000
            SELECT department, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department
            HAVING AVG(salary) >= 50000;

            -- Departments with maximum salary < 80000
            SELECT department, MAX(salary) AS max_salary
            FROM employees
            GROUP BY department
            HAVING MAX(salary) < 80000;

4. HAVING with Multiple Conditions

You can combine multiple conditions using AND or OR.

-- Departments with more than 5 employees and total salary > 200000
            SELECT department, COUNT(*) AS total_employees, SUM(salary) AS total_salary
            FROM employees
            GROUP BY department
            HAVING COUNT(*) > 5 AND SUM(salary) > 200000;

            -- Departments with fewer than 3 employees or avg salary > 60000
            SELECT department, COUNT(*) AS total_employees, AVG(salary) AS avg_salary
            FROM employees
            GROUP BY department
            HAVING COUNT(*) < 3 OR AVG(salary) > 60000;

5. Combining WHERE and HAVING

You can use WHERE to filter rows before aggregation and HAVINGto filter groups after aggregation.

-- IT department employees grouped by job title, only groups with more than 2 employees
            SELECT job_title, COUNT(*) AS total_employees
            FROM employees
            WHERE department = 'IT'
            GROUP BY job_title
            HAVING COUNT(*) > 2;

6. HAVING with JOINs

You can filter grouped results from joined tables.

-- Departments with total bonuses > 10000
            SELECT e.department, SUM(b.bonus) AS total_bonus
            FROM employees e
            JOIN bonuses b ON e.id = b.emp_id
            GROUP BY e.department
            HAVING SUM(b.bonus) > 10000;

7. HAVING vs WHERE

8. Best Practices

• Always use WHERE to filter rows first, then HAVING for groups.
• Use aggregate functions in HAVING carefully for performance.
• Combine with ORDER BY to sort aggregated results.
• Use indexes on columns used in WHERE for better efficiency.

Conclusion

The HAVING clause is essential for filtering aggregated data in PostgreSQL. It allows you to create more precise reports and analytics by applying conditions on groups. In the next tutorial, we will explore JOINs in PostgreSQL, which are used to combine data from multiple tables.