PostgreSQL HAVING

PostgreSQL `HAVING` Clause

The HAVING clause in PostgreSQL is used to filter aggregated results based on a condition. It is typically used with GROUP BY to filter the grouped rows after aggregation. While the WHERE clause filters rows before aggregation, the HAVING clause filters after aggregation.

1. Syntax of `HAVING`

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

aggregate_function(column3): The aggregation function, like COUNT(), SUM(), AVG(), MAX(), or MIN(), applied to column3.
HAVING: This filters the groups created by GROUP BY based on the result of the aggregate function.

2. Example: Using `HAVING` with `GROUP BY`

Example 1: Get Departments with More Than 5 Employees

Consider the following tables:

employees: Contains employee details (e.g., employee_id, department_id, salary).
departments: Contains department details (e.g., department_id, department_name).

Query: Retrieve Departments Having More Than 5 Employees

SELECT department_id, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5;

This query returns departments that have more than 5 employees. The HAVING clause filters the result of COUNT() after grouping the employees by department_id.

3. Example: Using `HAVING` with Other Aggregate Functions

Example 2: Get Products with a Total Sales Greater than 1000

Consider the sales table with the columns: product_id, quantity, and price.

Query: Retrieve Products with Total Sales Greater Than 1000

SELECT product_id, SUM(quantity * price) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(quantity * price) > 1000;

This query calculates the total sales for each product and returns products with sales greater than 1000. The HAVING clause filters the total sales after the aggregation.

4. Example: `HAVING` with `DISTINCT`

You can use HAVING to filter unique aggregated results.

Example 3: Get Customers Who Have Made More Than 3 Purchases

Consider the orders table with customer_id and order_id.

Query: Retrieve Customers Who Have Placed More Than 3 Orders

SELECT customer_id, COUNT(DISTINCT order_id) AS num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT order_id) > 3;

This query retrieves customers who have made more than 3 unique orders, filtering based on the count of distinct order_ids.

5. Combining `HAVING` with `WHERE`

WHERE filters rows before aggregation, while HAVING filters after aggregation.
You can use both WHERE and HAVING in the same query. WHERE filters rows before the grouping, and HAVING filters groups after aggregation.

Example 4: Filter Orders from the Year 2025 with Total Sales Greater than 1000

Consider the orders table with order_date, quantity, and price.

Query: Retrieve Orders from 2025 with Total Sales Greater Than 1000

SELECT EXTRACT(YEAR FROM order_date) AS order_year, SUM(quantity * price) AS total_sales
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025
GROUP BY order_year
HAVING SUM(quantity * price) > 1000;

In this query, WHERE filters the rows from the year 2025, and HAVING filters the total sales result after aggregation.

6. Performance Considerations

Efficiency: HAVING can slow down queries because it filters data after aggregation. For large datasets, ensure that indexes are used on columns involved in filtering and grouping to optimize performance.
HAVING vs. WHERE: Always prefer using WHERE for filtering rows before aggregation. Use HAVING when you need to filter based on aggregated data.

7. Summary of `HAVING`

Operation	SQL Command
Filter Groups After Aggregation	`SELECT ... FROM ... GROUP BY ... HAVING ...`
Filter Rows Before Aggregation	Use `WHERE` before the `GROUP BY` clause.
Usage	Use `HAVING` to filter results based on aggregated data like `COUNT()`, `SUM()`, `AVG()`, etc.

Would you like to see more complex examples or have questions about filtering data? 🚀

Souy Soeng

PostgreSQL HAVING