DocsAggregationsHAVING

HAVING

Aggregations

Concept

HAVING filters groups after aggregation, whereas WHERE filters individual rows before aggregation. This distinction is critical: if you need to exclude groups based on an aggregate value (e.g., only categories with more than 10 products), you must use HAVING because the aggregate does not exist at the WHERE stage.

HAVING can reference any aggregate expression, even ones not in the SELECT list. For example, you can write HAVING COUNT(*) > 5 without including COUNT(*) in your output columns. You can also combine multiple aggregate conditions with AND and OR.

A common pattern is WHERE + GROUP BY + HAVING used together: WHERE narrows the raw data, GROUP BY organizes it, and HAVING prunes the groups. Think of it as two stages of filtering with aggregation in between.

Syntax

-- Filter groups by aggregate value
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

-- Multiple aggregate conditions
SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
   AND SUM(total_amount) > 500;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Categories with average product price over $50
SELECT
  category,
  COUNT(*)   AS product_count,
  AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50
ORDER BY avg_price DESC;

-- Customers who placed at least 5 orders totaling over $1000
SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  COUNT(o.order_id)   AS orders,
  SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) >= 5
   AND SUM(o.total_amount) > 1000
ORDER BY total_spent DESC;

Common Pitfalls & Tips

1Do not put row-level conditions in HAVING — use WHERE instead. Putting them in HAVING works but forces the database to aggregate first, wasting resources.
2HAVING without GROUP BY is valid but rare; it treats the entire table as one group and filters based on the overall aggregate.
3You cannot reference SELECT aliases in HAVING in standard SQL. Repeat the aggregate expression or wrap the query in a subquery.

Practice HAVING queries