PostgreSQL HAVING

The HAVING clause in PostgreSQL is used to filter records that work with aggregate functions. It allows you to specify conditions on groups created by the GROUP BY clause.

Usage

The HAVING clause is employed when you need to filter groups of data in conjunction with aggregate functions like SUM, COUNT, or AVG. It comes after the GROUP BY clause and before ORDER BY if used.

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

In this syntax, HAVING condition filters the results returned by the GROUP BY clause based on aggregate function results. While WHERE filters rows before aggregation, HAVING applies conditions after aggregation.

Examples

1. Basic Group Filter

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
Mogelijk gemaakt door

This query lists departments with more than 10 employees by filtering groups created by the GROUP BY clause.

2. Using SUM with HAVING

SELECT product_id, SUM(quantity)
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;
Mogelijk gemaakt door

Here, the query retrieves products that have sold more than 100 units by filtering based on the sum of quantities.

3. Complex Condition with Multiple Aggregates

SELECT manager_id, COUNT(employee_id), AVG(salary)
FROM employees
GROUP BY manager_id
HAVING COUNT(employee_id) > 5 AND AVG(salary) < 70000;
Mogelijk gemaakt door

This example filters managers who oversee more than five employees and have an average salary below $70,000.

4. HAVING Without GROUP BY

SELECT SUM(quantity)
FROM sales
HAVING SUM(quantity) > 1000;
Mogelijk gemaakt door

This example demonstrates using HAVING without GROUP BY, filtering the entire dataset to find if the total quantity exceeds 1000 units.

Tips and Best Practices

• Use with GROUP BY. HAVING is designed to work with GROUP BY; ensure your query groups data appropriately, though it can also be used without it for entire dataset aggregation.
• Optimize with indexes. Consider indexing the columns used in the GROUP BY clause for performance improvements.
• Filter early with WHERE. Use the WHERE clause to filter rows before aggregation for efficiency.
• Avoid overuse. Use HAVING sparingly as filtering on aggregate results can be resource-intensive.