Filtering Groups with HAVING: PostgreSQL Analytics for Beginners

Previously in this course, we explored how to summarize data using aggregate functions and organize those summaries into buckets with the GROUP BY clause. In this lesson, we take that analysis a step further by learning how to filter those result sets, allowing you to hide specific groups that don't meet your business criteria.

The Problem with WHERE

When you work with aggregate functions like SUM(), COUNT(), or AVG(), you are often performing calculations across multiple rows. As you learned in our lesson on Filtering Data with WHERE, the WHERE clause filters rows before they are grouped or aggregated.

If you want to find customers who have spent more than $500 total, WHERE fails because the database doesn't know the "total spend" until it has processed all the orders. This is where HAVING comes in.

Understanding the HAVING Clause

The HAVING clause is essentially a WHERE clause for groups. It is applied after the GROUP BY operation, meaning it can "see" the results of your aggregate functions.

Worked Example: High-Value Customers

Let’s advance our store application by identifying our "VIP" customers. We want a list of customers who have placed at least three orders.

SQL
SELECT 
    customer_id, 
    COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) >= 3;

In this query:

1. FROM: The database accesses the orders table.
2. GROUP BY: It gathers all rows belonging to the same customer_id.
3. COUNT: It calculates the number of orders per customer.
4. HAVING: It discards any customer group where the COUNT is less than 3.

If you had tried to put COUNT(order_id) >= 3 in a WHERE clause, PostgreSQL would throw an error, because WHERE cannot evaluate aggregate functions.

Hands-on Exercise

Using our store schema, write a query to find product categories that have an average price of greater than $50. You will need to join the products table (which contains the price) and ensure you are grouping by the category field.

Hint: If you need a refresher on combining tables, refer back to our lesson on Understanding INNER JOIN.

Common Pitfalls

• Confusing WHERE and HAVING: The most common mistake is trying to use HAVING for non-aggregate conditions. While it can work, it is significantly slower because the database must group all data before filtering, rather than filtering rows immediately. Always use WHERE for non-aggregated columns.
• Column Availability: In the HAVING clause, you can only reference columns that are part of the GROUP BY clause or columns used in aggregate functions. Referencing other columns will cause an error.
• Ordering: Remember that HAVING must come after GROUP BY and before ORDER BY. Mixing up this sequence is a frequent cause of syntax errors.

FAQ

Q: Can I use both WHERE and HAVING in the same query? A: Yes. You use WHERE to filter individual rows (e.g., "only orders from 2023") and HAVING to filter the resulting groups (e.g., "only customers with more than 5 orders").

Q: Does HAVING affect performance? A: Yes. Because HAVING runs after grouping, it works on the intermediate result set. Large datasets that aren't filtered by a WHERE clause first can lead to high memory usage during the grouping phase.

Recap

The HAVING clause is your primary tool for filtering aggregate data. By placing your conditions after the GROUP BY clause, you can isolate specific trends or metrics—like identifying high-revenue products or frequent shoppers—that raw row-level filtering simply cannot see.

Up next: We will shift gears to handle semi-structured data by learning how to use JSONB for flexible data storage.