SQL 31 🛢️ Filtering Grouped Data with HAVING

The HAVING clause filters groups after aggregation. It is the counterpart to WHERE, which filters rows before grouping. A query that computes a total per customer, an average per department, or a count per day uses GROUP BY to create the groups, and HAVING to keep only the groups that meet a condition. Without HAVING, every group appears in the result, including the ones that are too small, too large, or otherwise uninteresting.

The distinction between WHERE and HAVING is one of the most commonly misunderstood aspects of SQL. WHERE cannot reference aggregates because aggregates do not exist until after grouping. HAVING can reference aggregates because it runs after grouping. A query that tries to filter on COUNT(*) in WHERE produces an error, and the fix is almost always to move the condition to HAVING. A query that filters on a column value can use either, but WHERE is more efficient because it reduces the number of rows before aggregation.

This chapter covers the syntax of HAVING, the difference between WHERE and HAVING, combining both clauses in a single query, HAVING with multiple conditions, HAVING with subqueries, and the performance considerations that determine when to filter before grouping versus after.

Key point: WHERE filters rows before grouping; HAVING filters groups after aggregation. HAVING can reference aggregate functions like COUNT, SUM, AVG, MIN, and MAX. A query can use both clauses: WHERE reduces the rows that go into the groups, and HAVING reduces the groups that appear in the result.

---

Why HAVING exists

The aggregate-filter problem. Once rows are grouped, the only way to filter the result is by the group’s properties. A group has a count, a sum, an average, a minimum, and a maximum. These values do not exist for individual rows, so they cannot be referenced in WHERE. HAVING exists specifically to filter on these aggregate values.

The WHERE limitation problem. WHERE is evaluated before GROUP BY. At that point, no aggregates have been computed, so any reference to an aggregate is an error. The database cannot know the count of a group before the group is formed. The rule is structural, not a matter of syntax.

The two-stage filtering problem. Some queries need to filter both before and after grouping. A report of high-value customers might first exclude customers with no orders (WHERE) and then keep only those whose total exceeds a threshold (HAVING). The two filters serve different purposes and cannot be combined into one clause.

The readability problem. A query that uses WHERE for row conditions and HAVING for group conditions expresses its intent clearly. A query that tries to do both with one clause either fails or becomes convoluted. Separating the two clarifies what the query is actually computing.

The performance problem. Filtering rows before grouping is cheaper than filtering groups after. Fewer rows to group means less work. A well-written query pushes as many conditions as possible into WHERE and reserves HAVING for conditions that genuinely require aggregate values.

---

a. Basic HAVING syntax

The HAVING clause appears after GROUP BY and before ORDER BY. It accepts conditions that reference either the grouping columns or aggregate functions.

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

This query returns only the departments with more than five employees. Departments with five or fewer are excluded from the result.

The condition can reference any aggregate:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 75000;

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

The condition can also reference grouping columns, though conditions on those columns are usually better placed in WHERE:

-- Works, but WHERE would be more efficient
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING department_id > 2;

The difference is performance, not correctness. HAVING department_id > 2 groups all rows and then discards groups; WHERE department_id > 2 discards rows before grouping, which is faster.

---

b. WHERE versus HAVING

The two clauses are evaluated at different stages of query processing.

Clause	Evaluated	Filters	Aggregates allowed
WHERE	Before GROUP BY	Rows	No
HAVING	After GROUP BY	Groups	Yes

A condition on a column belongs in WHERE. A condition on an aggregate belongs in HAVING. A condition that references an aggregate in WHERE is an error:

-- Error: aggregate functions are not allowed in WHERE
SELECT department_id, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department_id;

The error message varies by database, but the cause is the same: the aggregate does not exist when WHERE is evaluated.

The correct placement of a column condition and an aggregate condition in the same query uses both clauses:

SELECT department_id, COUNT(*) AS active_count
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) > 5;

The WHERE filters out inactive employees before grouping. The HAVING filters out departments with five or fewer active employees after grouping. Both conditions are necessary and neither can replace the other.

---

c. Multiple conditions in HAVING

HAVING accepts the same logical operators as WHERE: AND, OR, and NOT.

SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3 AND AVG(salary) > 60000;

This returns departments with at least three employees and an average salary above 60,000. Both conditions must hold for a group to appear.

Conditions can combine aggregates and grouping columns:

SELECT department_id, MAX(salary) AS top_salary
FROM employees
GROUP BY department_id
HAVING department_id IN (1, 2, 3) AND MAX(salary) > 90000;

The first condition references a grouping column and could be moved to WHERE for efficiency. The second references an aggregate and must remain in HAVING.

---

d. HAVING with subqueries

A HAVING condition can include a subquery that computes a threshold dynamically.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

This returns departments whose average salary exceeds the company-wide average. The subquery is evaluated once, and the result is used as the threshold for the HAVING condition.

A correlated subquery references the outer group:

SELECT department_id, COUNT(*) AS employee_count
FROM employees e
GROUP BY department_id
HAVING COUNT(*) > (
    SELECT AVG(dept_count)
    FROM (
        SELECT COUNT(*) AS dept_count
        FROM employees
        GROUP BY department_id
    ) AS counts
);

This returns departments with more employees than the average department. The subquery computes the average count across all departments, and the HAVING condition compares each department’s count to it.

---

e. HAVING without GROUP BY

HAVING can appear without GROUP BY, in which case the entire result set is treated as a single group.

SELECT COUNT(*) AS total_employees
FROM employees
HAVING COUNT(*) > 100;

This query returns one row if the total employee count exceeds 100, and zero rows otherwise. The result is the same as filtering in an outer query, but the syntax is valid.

This pattern is uncommon because a query without GROUP BY already returns a single row, and filtering it usually happens in the application or in an outer query. But it is legal and occasionally useful.

---

Complete Example Session

-- ============================================
-- PART 1: CREATE SAMPLE DATA
-- ============================================
CREATE TABLE employees (
    employee_id   INTEGER PRIMARY KEY,
    first_name    VARCHAR(50),
    department_id INTEGER,
    salary        NUMERIC(10, 2),
    active        BOOLEAN
);

INSERT INTO employees VALUES
(1, 'Alice',  1, 95000, TRUE),
(2, 'Bob',    1, 78000, TRUE),
(3, 'Carol',  1, 88000, TRUE),
(4, 'David',  1, 72000, TRUE),
(5, 'Eve',    2, 65000, TRUE),
(6, 'Frank',  2, 68000, TRUE),
(7, 'Grace',  2, 71000, FALSE),
(8, 'Henry',  3, 92000, TRUE),
(9, 'Ivy',    3, 85000, TRUE),
(10, 'Jack',  3, 79000, TRUE),
(11, 'Kate',  3, 95000, TRUE),
(12, 'Liam',  4, 60000, TRUE);

-- ============================================
-- PART 2: BASIC HAVING
-- ============================================
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3;

-- ============================================
-- PART 3: HAVING WITH AVG
-- ============================================
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 75000;

-- ============================================
-- PART 4: WHERE AND HAVING TOGETHER
-- ============================================
SELECT department_id, COUNT(*) AS active_count
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) >= 3;

-- ============================================
-- PART 5: MULTIPLE HAVING CONDITIONS
-- ============================================
SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3 AND AVG(salary) > 70000;

-- ============================================
-- PART 6: HAVING WITH SUBQUERY
-- ============================================
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

-- ============================================
-- PART 7: HAVING WITH GROUPING COLUMN
-- ============================================
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING department_id IN (1, 2) AND SUM(salary) > 300000;

-- ============================================
-- PART 8: HAVING WITHOUT GROUP BY
-- ============================================
SELECT COUNT(*) AS total_employees
FROM employees
HAVING COUNT(*) > 100;
-- Returns no rows because count is 12

-- ============================================
-- PART 9: HAVING WITH ORDER BY
-- ============================================
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2
ORDER BY employee_count DESC;

-- ============================================
-- PART 10: COMPLETE FILTERING PIPELINE
-- ============================================
SELECT department_id,
       COUNT(*) AS active_count,
       AVG(salary) AS avg_salary,
       SUM(salary) AS total_salary
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) >= 3 AND AVG(salary) > 70000
ORDER BY avg_salary DESC;

These ten parts cover basic HAVING, HAVING with AVG, WHERE and HAVING together, multiple conditions, subqueries, grouping column conditions, HAVING without GROUP BY, HAVING with ORDER BY, and a complete filtering pipeline.

---

Quick Reference

HAVING Syntax

Clause	Position
SELECT	First
FROM	Second
WHERE	Before GROUP BY
GROUP BY	After WHERE
HAVING	After GROUP BY
ORDER BY	Last

WHERE vs HAVING

Aspect	WHERE	HAVING
Evaluated	Before grouping	After grouping
Filters	Rows	Groups
Aggregates	Not allowed	Allowed
Use for	Column conditions	Aggregate conditions

Common HAVING Conditions

Condition	Meaning
COUNT(*) > 5	More than 5 rows in group
SUM(x) >= 1000	Total at least 1000
AVG(x) < 50	Average below 50
MIN(x) > 0	No zero or negative values
MAX(x) <= 100	No value above 100

Logical Operators

Operator	Example
AND	HAVING COUNT(*) > 3 AND AVG(salary) > 50000
OR	HAVING COUNT(*) > 10 OR SUM(total) > 100000
NOT	HAVING NOT (COUNT(*) = 1)

---

Best Practices

✅ Do This:

-- Filter rows in WHERE, groups in HAVING
SELECT department_id, COUNT(*)
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) > 5;

-- Use aggregate in HAVING
HAVING AVG(salary) > 75000

-- Combine conditions
HAVING COUNT(*) >= 3 AND AVG(salary) > 60000

❌ Don’t Do This:

-- Aggregate in WHERE
WHERE COUNT(*) > 5  -- ❌ error

-- Column condition in HAVING when WHERE works
HAVING active = TRUE  -- ❌ less efficient

-- HAVING without GROUP BY when not needed
SELECT COUNT(*) FROM employees HAVING COUNT(*) > 0;  -- ❌ unusual

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Aggregate in WHERE	Confusion about evaluation order	Move to HAVING
Column condition in HAVING	Not knowing WHERE is cheaper	Move to WHERE
Non-aggregated column not in GROUP BY	Missing from GROUP BY	Add to GROUP BY or aggregate
HAVING without GROUP BY	Unintended single group	Add GROUP BY or remove HAVING
Subquery returns multiple rows	Subquery not aggregated	Use aggregate in subquery
HAVING references alias	Alias not available at that stage	Repeat the expression

---

Real-World Examples

1. Departments with Many Employees

SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

2. Customers with High Lifetime Value

SELECT customer_id, SUM(total) AS lifetime
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 10000;

3. Products with Low Average Rating

SELECT product_id, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_id
HAVING AVG(rating) < 3.0;

4. Days with Many Orders

SELECT DATE(order_date) AS day, COUNT(*) AS orders
FROM orders
GROUP BY DATE(order_date)
HAVING COUNT(*) > 100;

5. Active Users per Region

SELECT region, COUNT(*) AS active_users
FROM users
WHERE active = TRUE
GROUP BY region
HAVING COUNT(*) >= 50;

6. Categories with No Sales

SELECT category_id, SUM(quantity) AS sold
FROM order_items
GROUP BY category_id
HAVING SUM(quantity) IS NULL OR SUM(quantity) = 0;

7. Above-Average Departments

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

8. Repeat Customers

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

9. High-Value Orders per Customer

SELECT customer_id, COUNT(*) AS big_orders
FROM orders
WHERE total > 1000
GROUP BY customer_id
HAVING COUNT(*) >= 3;

10. Combined Row and Group Filter

SELECT department_id,
       COUNT(*) AS active_count,
       AVG(salary) AS avg_salary
FROM employees
WHERE active = TRUE
GROUP BY department_id
HAVING COUNT(*) >= 3 AND AVG(salary) > 70000
ORDER BY avg_salary DESC;

---

Visual

Query Processing Order

┌──────────────────────────────────────────────────────────────┐
│  1. FROM / JOIN      ──▶ Assemble rows                       │
│  2. WHERE            ──▶ Filter individual rows              │
│  3. GROUP BY         ──▶ Partition remaining rows            │
│  4. Aggregates       ──▶ Compute per group                   │
│  5. HAVING           ──▶ Filter groups                       │
│  6. SELECT           ──▶ Produce output columns              │
│  7. ORDER BY         ──▶ Sort output                         │
│                                                              │
│  WHERE filters before step 3; HAVING filters after step 4.   │
└──────────────────────────────────────────────────────────────┘

WHERE vs HAVING

┌──────────────────────────────────────────────────────────────┐
│  ALL ROWS                                                    │
│       │                                                      │
│       ▼                                                      │
│  ┌────────────────────────────────────────────────────────┐  │
│  │  WHERE active = TRUE                                   │  │
│  │  (filters rows)                                        │  │
│  └────────────────────────┬───────────────────────────────┘  │
│                           │                                   │
│                           ▼                                   │
│  ┌────────────────────────────────────────────────────────┐  │
│  │  GROUP BY department_id                                │  │
│  └────────────────────────┬───────────────────────────────┘  │
│                           │                                   │
│                           ▼                                   │
│  ┌────────────────────────────────────────────────────────┐  │
│  │  HAVING COUNT(*) > 5                                   │  │
│  │  (filters groups)                                      │  │
│  └────────────────────────┬───────────────────────────────┘  │
│                           │                                   │
│                           ▼                                   │
│  FINAL RESULT                                                │
└──────────────────────────────────────────────────────────────┘

HAVING Without GROUP BY

┌──────────────────────────────────────────────────────────────┐
│  SELECT COUNT(*) AS total                                    │
│  FROM employees                                              │
│  HAVING COUNT(*) > 100;                                      │
│                                                              │
│  Entire result set treated as one group.                     │
│  Returns 1 row if count > 100, 0 rows otherwise.             │
└──────────────────────────────────────────────────────────────┘

---

Summary

Item	Value
HAVING	Filters groups after aggregation
Position	After GROUP BY, before ORDER BY
References	Aggregate functions and grouping columns
WHERE	Filters rows before grouping
Aggregates in WHERE	Not allowed
Aggregates in HAVING	Allowed
Multiple conditions	AND, OR, NOT
Subqueries	Allowed in HAVING
Without GROUP BY	Treats entire result as one group
Performance	Push row conditions to WHERE

Key takeaways:

• HAVING filters groups after aggregation. It is the only clause that can reference aggregate functions in a condition. COUNT(*) > 5 and AVG(salary) > 75000 are valid HAVING conditions.
• WHERE filters rows before grouping. It cannot reference aggregates because they do not exist until after grouping. WHERE COUNT(*) > 5 is a syntax or semantic error.
• A query can use both clauses. WHERE reduces the rows that go into the groups, and HAVING reduces the groups that appear in the result. The two filters serve different purposes.
• Column conditions belong in WHERE for performance. HAVING department_id > 2 works, but WHERE department_id > 2 is more efficient because it discards rows before grouping.
• HAVING supports subqueries. A subquery can compute a dynamic threshold, such as the company-wide average salary, against which each group is compared.
• HAVING can appear without GROUP BY. In that case, the entire result set is treated as a single group, and the HAVING condition determines whether that group appears.

Remember: HAVING is the clause that filters groups. It exists because WHERE cannot reference aggregates, and aggregates are the only values that describe a group. The rule is simple: conditions on individual rows go in WHERE; conditions on group values go in HAVING. When both are needed, both clauses appear. For performance, push as much filtering as possible into WHERE, because fewer rows to group means less work. Use HAVING only for the conditions that genuinely require aggregate values. Subqueries in HAVING allow dynamic thresholds, and multiple conditions combine with the same logical operators as WHERE. Understanding HAVING completes the picture of grouped queries: GROUP BY creates the groups, aggregates compute their values, and HAVING decides which ones appear.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!