Filtering Groups with HAVING

In SQL, GROUP BY creates groups of rows, and aggregate functions calculate summaries for those groups.

But what if you only want groups that satisfy a condition?

For example:
- Show departments with more than five employees.
- Show customers whose total spending is at least 10,000.
- Show product categories with average prices above 500.
- Show cities with at least 100 registered users.

This is where HAVING is useful.

HAVING filters groups according to conditions that are evaluated using grouped results.

Think of the process this way:

1. Start with the source rows.
2. Optionally filter individual rows using WHERE.
3. Group the remaining rows using GROUP BY.
4. Calculate aggregates such as COUNT or SUM.
5. Keep only the groups that satisfy HAVING.
6. Return and optionally sort the results.

This lesson uses PostgreSQL syntax.

The basic syntax is:

SELECT grouping_column, aggregate_function(column)
FROM table_name
GROUP BY grouping_column
HAVING aggregate_condition;

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

The query returns only departments containing more than five employee rows.

Important:
- GROUP BY determines which rows belong together.
- COUNT(*) calculates the number of rows in each department.
- HAVING checks the count for each department.
- Groups with a count of five or fewer are excluded.

The employees table and its department column must exist in your database.

Suppose an orders table contains:

| order_id | category | amount |
|---:|---|---:|
| 1 | Books | 100 |
| 2 | Books | 200 |
| 3 | Electronics | 500 |
| 4 | Electronics | 300 |
| 5 | Electronics | 200 |
| 6 | Clothing | 150 |

Requirement: Show categories with more than two orders.

First, GROUP BY category creates three groups:

Books: 2 orders
Electronics: 3 orders
Clothing: 1 order

Next, HAVING COUNT(*) > 2 checks each group.

Books is excluded because its count is 2.
Electronics is included because its count is 3.
Clothing is excluded because its count is 1.

The result contains only Electronics.

HAVING filters the groups; it does not delete or modify the original table rows.

WHERE and HAVING both filter data, but they filter at different stages.

WHERE:
- Filters individual rows before grouping.
- Commonly checks ordinary column values.
- Determines which rows participate in aggregates.

HAVING:
- Filters groups after grouping.
- Commonly checks aggregate results.
- Determines which grouped results are returned.

Example requirement:
Show completed-order categories whose completed-order total is greater than 1,000.

SELECT
category,
SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING SUM(amount) > 1000;

Here, WHERE excludes orders that are not completed. GROUP BY forms category groups from the remaining rows. HAVING then excludes categories whose completed-order total is not greater than 1,000.

A useful rule:
WHERE asks, "Which rows should be included?"
HAVING asks, "Which groups should remain?"

Consider this query:

SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department;

In PostgreSQL, this produces an error because aggregate functions are not allowed in the WHERE clause at that query level.

Why?

WHERE filters individual rows before grouping. At that stage, the final count for each department has not been calculated.

Use HAVING instead:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Remember the conceptual processing order:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

This is a logical explanation of query processing, not a guarantee of the database's physical execution order.

COUNT is often used to filter groups by the number of rows or non-NULL values.

Examples:

HAVING COUNT(*) >= 10
Keeps groups with at least ten rows.

HAVING COUNT(*) = 1
Keeps groups containing exactly one row.

HAVING COUNT(email) > 5
Keeps groups with more than five non-NULL email values.

HAVING COUNT(DISTINCT customer_id) > 3
Keeps groups containing more than three distinct non-NULL customer IDs.

Choose the COUNT expression according to the question. COUNT(*) and COUNT(column) do not necessarily return the same result when NULL values exist.

SUM is useful when the condition depends on a group's total.

For example, show customers whose total spending is at least 10,000:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 10000;

The threshold is checked against each customer's total, not against each individual order.

This distinction matters. A customer may have several small orders whose combined total exceeds the threshold even though no individual order does.

HAVING can use any suitable aggregate function.

Examples:

HAVING AVG(salary) > 50000
Keeps groups whose average salary exceeds 50,000.

HAVING MIN(price) >= 100
Keeps groups whose minimum price is at least 100.

HAVING MAX(amount) > 5000
Keeps groups containing a maximum amount greater than 5,000.

These conditions describe a property of the group, not necessarily every row in that group.

For example, AVG(salary) > 50000 does not mean that every employee in the department earns more than 50,000.

Many useful reports combine all three clauses.

Scenario:
Find product categories whose completed orders total at least 1,000.

Step 1: WHERE keeps only completed orders.
Step 2: GROUP BY forms one group per category.
Step 3: SUM calculates the completed-order total for each category.
Step 4: HAVING keeps categories with totals of at least 1,000.

The order matters because filtering rows before aggregation can change the aggregate value.

HAVING can combine multiple aggregate conditions using AND and OR.

Example using AND:

HAVING COUNT(*) >= 5
AND SUM(amount) >= 1000

A group must satisfy both conditions.

Example using OR:

HAVING COUNT(*) >= 10
OR SUM(amount) >= 5000

A group is kept if at least one condition is true.

Use parentheses whenever they make the intended logic clearer, especially when combining AND and OR.

Remember that aggregate functions such as SUM can return NULL when all their input values are NULL. Comparisons involving that NULL result may evaluate to UNKNOWN rather than TRUE.

Suppose the requirement is:

Keep a category if it has at least ten orders, or if its total sales are at least 5,000 and it has at least three orders.

Write:

HAVING COUNT(*) >= 10
OR (
SUM(amount) >= 5000
AND COUNT(*) >= 3
)

The parentheses make the intended relationship explicit.

Without careful grouping of AND and OR conditions, a query can return groups that do not meet the business rule.

The same logical principles used in WHERE apply to HAVING.

NULL values can affect aggregate conditions.

Consider:

SELECT department, SUM(bonus) AS total_bonus
FROM employees
GROUP BY department
HAVING SUM(bonus) > 0;

SUM(bonus) ignores NULL bonus values. If every bonus in a department is NULL, SUM(bonus) returns NULL.

The comparison NULL > 0 evaluates to UNKNOWN, so that group does not pass the HAVING condition.

Compare these expressions:

COUNT(*) counts every row.
COUNT(bonus) counts rows with a non-NULL bonus.
SUM(bonus) adds non-NULL bonus values.
COALESCE(SUM(bonus), 0) replaces a NULL sum with zero.

Do not automatically replace NULL with zero unless zero is the correct meaning for your application. A missing bonus and a confirmed zero bonus may represent different business situations.

Yes. In PostgreSQL, HAVING can be used without an explicit GROUP BY.

For example:

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;

Without GROUP BY, the aggregate query treats the input as one overall group. HAVING determines whether that group is returned.

If the table has more than 100 rows, the result contains one row. Otherwise, no row is returned.

This is different from a grouped query, where HAVING evaluates each group separately.

Use this pattern when you specifically need to filter a single overall aggregate result.

Consider:

SELECT category, SUM(amount) AS total_sales
FROM orders
GROUP BY category
HAVING SUM(amount) >= 1000;

This is a clear, portable style for filtering with an aggregate.

In PostgreSQL, do not assume that the SELECT output alias total_sales is available in HAVING at the same query level:

HAVING total_sales >= 1000

That reference generally fails because HAVING is resolved before the SELECT output alias is available.

Use the aggregate expression in HAVING, or introduce a subquery or CTE when you want to filter by a named aggregate result.

DISTINCT and HAVING solve different problems.

DISTINCT removes duplicate rows from the selected result.

GROUP BY creates groups and supports aggregate calculations.

HAVING filters groups based on conditions.

Example: list distinct cities:

SELECT DISTINCT city
FROM customers;

Example: find cities with at least 100 customers:

SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) >= 100;

If you only need unique values, DISTINCT is usually the more direct choice. If you need a summary per value or need to filter by an aggregate, GROUP BY with HAVING is appropriate.

HAVING is not a substitute for filtering source rows early when the requirement allows a WHERE condition.

For example, if you only want completed orders, use:

WHERE status = 'Completed'

before grouping, rather than aggregating all statuses and attempting to exclude unrelated rows afterward.

This can reduce the number of rows that need to be aggregated.

However, a condition involving an aggregate such as SUM(amount) >= 1000 must be evaluated at the group level and generally belongs in HAVING or an outer query.

Performance depends on the actual query, indexes, data distribution, and execution plan. Do not assume that HAVING is always slow or that WHERE always uses an index.

Use EXPLAIN to inspect the plan. Use EXPLAIN ANALYZE when actual execution is appropriate and safe for your environment.

Assume these tables exist:

employees(employee_id, department, salary)

orders(order_id, customer_id, category, status, amount)

Write a query for each task.

1. Show departments with more than five employees.

2. Show customers whose total order amount is at least 10,000.

3. Show categories with at least ten orders.

4. Show categories whose average order amount is greater than 500.

5. Show only completed-order categories with total sales of at least 2,000.

6. Show departments with at least three employees and an average salary above 40,000.

7. Show customers with more than two orders and total spending above 5,000.

8. Explain why WHERE COUNT(*) > 5 is invalid in a normal grouped aggregate query.

9. Explain the difference between COUNT(*) and COUNT(salary).

10. Can HAVING be used without GROUP BY? Explain how it behaves.

Try writing the queries before checking the solutions.

1. Departments with more than five employees:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

2. Customers spending at least 10,000:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 10000;

3. Categories with at least ten orders:

SELECT category, COUNT(*) AS order_count
FROM orders
GROUP BY category
HAVING COUNT(*) >= 10;

4. Categories with average order amount above 500:

SELECT category, AVG(amount) AS average_amount
FROM orders
GROUP BY category
HAVING AVG(amount) > 500;

5. Completed-order categories with total sales of at least 2,000:

SELECT category, SUM(amount) AS total_sales
FROM orders
WHERE status = 'Completed'
GROUP BY category
HAVING SUM(amount) >= 2000;

6. Departments with at least three employees and average salary above 40,000:

SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3
AND AVG(salary) > 40000;

7. Customers with more than two orders and spending above 5,000:

SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2
AND SUM(amount) > 5000;

8. WHERE filters source rows before aggregate calculations, so aggregate conditions belong in HAVING or an appropriate outer query.

9. COUNT(*) counts all rows. COUNT(salary) counts rows where salary is not NULL.

10. Yes. Without GROUP BY, an aggregate query can treat all input rows as one group. HAVING decides whether that overall result is returned.

Choose the best answer for each question. Correct answers and explanations are provided.

Q1. What is HAVING?
Answer: HAVING filters groups based on conditions, commonly involving aggregate functions.

Q2. What is the difference between WHERE and HAVING?
Answer: WHERE filters individual rows before grouping; HAVING filters groups after grouping.

Q3. Why can't COUNT(*) normally be used directly in WHERE?
Answer: WHERE operates before the aggregate result is calculated. Use HAVING for group-level aggregate conditions.

Q4. Can HAVING be used without GROUP BY?
Answer: Yes. In PostgreSQL, an aggregate query without GROUP BY can use HAVING to decide whether its single overall aggregate result is returned.

Q5. How do you find departments with more than five employees?
Answer: GROUP BY department and use HAVING COUNT(*) > 5.

Q6. How do you find customers whose total spending is at least 10,000?
Answer: Group orders by customer_id and use HAVING SUM(amount) >= 10000.

Q7. Can HAVING contain multiple conditions?
Answer: Yes. Use AND, OR, and parentheses to express the required group-level logic.

Q8. Can WHERE and HAVING be used together?
Answer: Yes. WHERE filters source rows, and HAVING filters the groups formed from those rows.

Q9. Does HAVING guarantee sorted output?
Answer: No. Use ORDER BY.

Q10. How does SUM handle NULL?
Answer: SUM ignores NULL inputs. If all inputs are NULL, it returns NULL.

Q11. What happens when HAVING excludes every group?
Answer: The query returns no result rows.

Q12. Can HAVING use AVG, MIN, and MAX?
Answer: Yes. Any suitable aggregate expression can be used in a HAVING condition.

Q13. Is a SELECT alias always available in PostgreSQL HAVING?
Answer: No. At the same query level, use the aggregate expression directly or wrap the query in a subquery or CTE.

Q14. Why might WHERE improve an aggregate query?
Answer: It can reduce the number of source rows that need to be grouped and aggregated.

Q15. Does HAVING always make a query slower?
Answer: No. Performance depends on the query, data, execution plan, and other factors.

Q16. What is the logical query processing order relevant to HAVING?
Answer: A useful conceptual order is FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.

Q17. What is the difference between COUNT(*) and COUNT(column)?
Answer: COUNT(*) counts all rows, whereas COUNT(column) ignores rows where the column is NULL.

Q18. Why should parentheses be used with AND and OR?
Answer: They make the intended logical grouping explicit and help prevent incorrect filtering.

Q19. How do you investigate a slow HAVING query?
Answer: Inspect its execution plan, source-row volume, filters, joins, and aggregation strategy.

Q20. What should be decided before using HAVING?
Answer: Decide what each output group represents and which aggregate condition determines whether that group should be returned.

You have learned how to filter grouped SQL results with HAVING.

Remember these rules:

- GROUP BY creates groups.
- Aggregate functions calculate values for each group.
- HAVING filters groups based on conditions.
- WHERE filters source rows before grouping.
- WHERE and HAVING can be used together.
- COUNT, SUM, AVG, MIN, and MAX are common aggregate functions.
- Use AND, OR, and parentheses carefully.
- NULL values can affect aggregate results and comparisons.
- In PostgreSQL, use aggregate expressions directly in HAVING rather than relying on a same-level SELECT alias.
- Use ORDER BY when the output order matters.

A useful pattern to remember:

SELECT grouping_column, COUNT(*) AS row_count
FROM table_name
WHERE row_condition
GROUP BY grouping_column
HAVING COUNT(*) > threshold
ORDER BY row_count DESC;

First decide which rows should be included, then how they should be grouped, and finally which groups should remain.

Avoid these frequent mistakes when filtering grouped query results.