Most SQL practitioners use HAVING only for the simplest cases. This lesson goes deeper — teaching conditional aggregates with CASE WHEN, dynamic thresholds with subqueries, and real-world churn analysis patterns that WHERE simply cannot express.
Imagine you're analyzing sales data for a retail chain. Your manager asks: "Which product categories generated more than $50,000 in revenue last quarter, and how many distinct customers bought from each?" You reach for WHERE, and then realize — WHERE can't answer this question. You can't filter on total revenue before the totals exist. The aggregation has to happen first, and then you filter.
That's exactly what HAVING is for. It's the clause that lets you apply conditions to groups after aggregation, unlocking an entire class of analytical questions that WHERE simply cannot touch. Most practitioners know HAVING exists, but use it only for the simplest cases: "give me groups where COUNT > 5." The real power of HAVING goes much further — conditional aggregates, multi-condition group filters, ranking by aggregate values, and combining HAVING with subqueries to create remarkably expressive queries.
By the end of this lesson, you'll be writing HAVING clauses with the same confidence you bring to WHERE. You'll understand not just the syntax but the execution model that explains why HAVING works the way it does, and you'll know exactly when to reach for it versus its alternatives.
What you'll learn:
HAVING exists and how it differs fundamentally from WHERE in SQL's execution orderCASE WHEN inside aggregate functionsHAVING clauses that express complex business logicHAVING with subqueriesWHEREThis lesson assumes you're comfortable with:
SELECT, FROM, and WHERE clauses — if you need a refresher, see SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First QueriesGROUP BY and basic aggregate functions (COUNT, SUM, AVG, MIN, MAX) — if those feel shaky, start with Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for BeginnersJOIN syntaxTo understand HAVING, you have to understand that SQL queries don't execute in the order you write them. The logical order of execution is:
1. FROM / JOIN → identify the source tables and combine rows
2. WHERE → filter individual rows before grouping
3. GROUP BY → collapse rows into groups
4. HAVING → filter groups after aggregation
5. SELECT → compute output expressions
6. ORDER BY → sort the results
7. LIMIT / OFFSET → trim the result set
WHERE runs at step 2, before groups exist. That's why you can't write WHERE SUM(amount) > 50000 — there's no sum yet. The aggregate hasn't been computed because the rows haven't been grouped yet.
HAVING runs at step 4, after groups are formed and aggregates are calculated. At that point, SUM(amount), COUNT(*), AVG(price) — all of these are fully resolved values you can compare against.
Key insight
Think of WHERE as a row-level filter and HAVING as a group-level filter. They operate on different objects at different points in the query lifecycle. Using the wrong one doesn't just give you an error — it means you're thinking about the problem incorrectly.
Here's the simplest demonstration:
-- This fails: WHERE cannot reference an aggregate
SELECT
category,
SUM(revenue) AS total_revenue
FROM sales
WHERE SUM(revenue) > 50000 -- ❌ ERROR: aggregate functions not allowed in WHERE
GROUP BY category;
-- This works: HAVING operates after aggregation
SELECT
category,
SUM(revenue) AS total_revenue
FROM sales
GROUP BY category
HAVING SUM(revenue) > 50000; -- ✅ Correct
Note
Some databases let you reference a SELECT alias in HAVING (e.g., HAVING total_revenue > 50000), but this isn't standard SQL and isn't portable. MySQL supports it; PostgreSQL and SQL Server generally don't. Writing the full expression in HAVING is safer and more explicit.
Let's work with a realistic dataset throughout this lesson. We have a B2B software company with the following tables:
-- Accounts represent customers (companies)
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_name VARCHAR(100),
industry VARCHAR(50),
region VARCHAR(30)
);
-- Subscriptions represent software licenses sold
CREATE TABLE subscriptions (
subscription_id INT PRIMARY KEY,
account_id INT,
plan_type VARCHAR(20), -- 'starter', 'professional', 'enterprise'
monthly_value DECIMAL(10,2),
start_date DATE,
end_date DATE, -- NULL if still active
sales_rep_id INT
);
-- Renewals track subscription renewal events
CREATE TABLE renewals (
renewal_id INT PRIMARY KEY,
subscription_id INT,
renewal_date DATE,
renewed BOOLEAN, -- TRUE if renewed, FALSE if churned
renewal_value DECIMAL(10,2)
);
-- Sales reps
CREATE TABLE sales_reps (
sales_rep_id INT PRIMARY KEY,
rep_name VARCHAR(100),
team VARCHAR(50)
);
This gives us enough complexity to write genuinely interesting HAVING clauses.
Let's start with the fundamentals before building toward complexity. Which industries have more than 10 active subscriptions?
SELECT
a.industry,
COUNT(s.subscription_id) AS active_subscriptions,
SUM(s.monthly_value) AS total_mrr
FROM accounts a
JOIN subscriptions s
ON a.account_id = s.account_id
WHERE s.end_date IS NULL -- only active subscriptions
GROUP BY a.industry
HAVING COUNT(s.subscription_id) > 10
ORDER BY total_mrr DESC;
Notice the interplay between WHERE and HAVING here:
WHERE s.end_date IS NULL filters rows before grouping — we only group active subscriptionsHAVING COUNT(...) > 10 filters the resulting groupsThis is a key pattern: use WHERE to reduce the row pool, then use HAVING to filter the aggregated results. Applying WHERE first is also better for performance because it reduces the number of rows the database has to group.
Tip
Whenever you catch yourself wanting to filter on an aggregate, that's your signal to use HAVING. If you're filtering on a plain column value, WHERE is almost always the right choice — and it's faster because it runs before grouping.
Single-condition HAVING clauses are fine, but real analytical questions often require multiple conditions. Suppose you want to find industries that are both high-value and broadly adopted — where total monthly recurring revenue exceeds $100,000 AND the average subscription value per account is above $2,000:
SELECT
a.industry,
COUNT(DISTINCT a.account_id) AS customer_count,
COUNT(s.subscription_id) AS subscription_count,
SUM(s.monthly_value) AS total_mrr,
AVG(s.monthly_value) AS avg_subscription_value
FROM accounts a
JOIN subscriptions s
ON a.account_id = s.account_id
WHERE s.end_date IS NULL
GROUP BY a.industry
HAVING
SUM(s.monthly_value) > 100000
AND AVG(s.monthly_value) > 2000
ORDER BY total_mrr DESC;
You can also use OR in HAVING, though it's less common. Here's a scenario where it makes sense: flag industries that either have suspiciously few subscriptions per customer (potential churn risk) or very low average values (possible underpricing):
SELECT
a.industry,
COUNT(DISTINCT a.account_id) AS customer_count,
COUNT(s.subscription_id) AS subscription_count,
ROUND(COUNT(s.subscription_id)::NUMERIC /
NULLIF(COUNT(DISTINCT a.account_id), 0), 2) AS subs_per_customer,
ROUND(AVG(s.monthly_value), 2) AS avg_monthly_value
FROM accounts a
JOIN subscriptions s
ON a.account_id = s.account_id
WHERE s.end_date IS NULL
GROUP BY a.industry
HAVING
(COUNT(s.subscription_id)::NUMERIC /
NULLIF(COUNT(DISTINCT a.account_id), 0)) < 1.2
OR AVG(s.monthly_value) < 500
ORDER BY avg_monthly_value;
Warning
When you divide inside HAVING, always guard against division by zero. NULLIF(COUNT(DISTINCT a.account_id), 0) returns NULL instead of zero, which prevents a runtime error. A NULL comparison in HAVING evaluates to false (the group is excluded), which is the behavior you want here.
This is where HAVING gets genuinely powerful. You can embed CASE WHEN logic inside aggregate functions in the HAVING clause, letting you express conditions like "at least 30% of subscriptions must be enterprise-tier."
If you haven't explored this pattern before, the idea is straightforward: CASE WHEN inside SUM() or COUNT() effectively counts or sums only the rows that match a condition. For a deeper treatment of this technique, see Conditional Aggregation with CASE WHEN: Pivoting Logic Without Reshaping Your Data.
Here's that enterprise-tier example:
SELECT
a.industry,
COUNT(s.subscription_id) AS total_subscriptions,
SUM(CASE WHEN s.plan_type = 'enterprise' THEN 1 ELSE 0 END)
AS enterprise_count,
ROUND(
100.0 * SUM(CASE WHEN s.plan_type = 'enterprise' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(s.subscription_id), 0),
1
) AS enterprise_pct
FROM accounts a
JOIN subscriptions s
ON a.account_id = s.account_id
WHERE s.end_date IS NULL
GROUP BY a.industry
HAVING
SUM(CASE WHEN s.plan_type = 'enterprise' THEN 1 ELSE 0 END) * 1.0
/ NULLIF(COUNT(s.subscription_id), 0) >= 0.30
ORDER BY enterprise_pct DESC;
The HAVING clause here filters to industries where the enterprise plan makes up at least 30% of active subscriptions. You're computing a ratio entirely within the HAVING clause — no subquery required.
Let's look at another practical example: find sales reps who have strong renewal performance. Specifically, reps who handled at least 20 renewal events AND achieved a renewal rate above 80%:
SELECT
sr.rep_name,
sr.team,
COUNT(r.renewal_id) AS total_renewals,
SUM(CASE WHEN r.renewed = TRUE THEN 1 ELSE 0 END) AS successful_renewals,
ROUND(
100.0 * SUM(CASE WHEN r.renewed = TRUE THEN 1 ELSE 0 END)
/ NULLIF(COUNT(r.renewal_id), 0),
1
) AS renewal_rate_pct
FROM sales_reps sr
JOIN subscriptions s ON sr.sales_rep_id = s.sales_rep_id
JOIN renewals r ON s.subscription_id = r.subscription_id
GROUP BY sr.sales_rep_id, sr.rep_name, sr.team
HAVING
COUNT(r.renewal_id) >= 20
AND SUM(CASE WHEN r.renewed = TRUE THEN 1 ELSE 0 END) * 1.0
/ NULLIF(COUNT(r.renewal_id), 0) > 0.80
ORDER BY renewal_rate_pct DESC;
This query would be impossible to write cleanly with WHERE alone. The conditions are inherently aggregate — they describe properties of groups of rows, not individual rows.
Key insight
CASE WHEN inside SUM() is essentially a conditional counter. SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts rows where the condition is true. In PostgreSQL, you can also write this more concisely as COUNT(*) FILTER (WHERE condition) — but SUM(CASE WHEN...) works across virtually all SQL dialects.
Static thresholds in HAVING are useful, but sometimes the threshold itself needs to be computed. For example: "find sales reps whose total closed revenue exceeds the company-wide average for all reps." The average changes as data changes, so you don't want to hardcode a number.
This is where HAVING with a subquery earns its place:
SELECT
sr.rep_name,
sr.team,
SUM(s.monthly_value * 12) AS annual_contract_value
FROM sales_reps sr
JOIN subscriptions s ON sr.sales_rep_id = s.sales_rep_id
WHERE s.end_date IS NULL
GROUP BY sr.sales_rep_id, sr.rep_name, sr.team
HAVING SUM(s.monthly_value * 12) > (
SELECT AVG(rep_acv)
FROM (
SELECT SUM(monthly_value * 12) AS rep_acv
FROM subscriptions
WHERE end_date IS NULL
GROUP BY sales_rep_id
) AS rep_totals
)
ORDER BY annual_contract_value DESC;
The subquery in HAVING computes the average ACV (Annual Contract Value) per rep across all reps. The outer query then filters to only those reps who beat that average. Both pieces are fully dynamic — change the data and the threshold adjusts automatically.
For a deeper look at subquery patterns in SQL, Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements covers the mechanics thoroughly.
Warning
Subqueries in HAVING execute once per query (they're non-correlated here), so performance is usually fine. But always verify using your database's query plan. If the subquery is correlated and references the outer query, it can execute once per group — which gets expensive fast.
Before window functions became standard, HAVING combined with subqueries was the way to find top-N groups. Even today, this pattern is worth knowing for databases with limited window function support, and it makes the logic explicit in a useful way.
Find the top 5 industries by total MRR:
SELECT
a.industry,
SUM(s.monthly_value) AS total_mrr
FROM accounts a
JOIN subscriptions s ON a.account_id = s.account_id
WHERE s.end_date IS NULL
GROUP BY a.industry
HAVING SUM(s.monthly_value) >= (
SELECT MIN(industry_mrr)
FROM (
SELECT SUM(s2.monthly_value) AS industry_mrr
FROM accounts a2
JOIN subscriptions s2 ON a2.account_id = s2.account_id
WHERE s2.end_date IS NULL
GROUP BY a2.industry
ORDER BY industry_mrr DESC
LIMIT 5
) AS top5
)
ORDER BY total_mrr DESC;
The inner subquery identifies the minimum MRR among the top 5 industries. The outer HAVING then keeps only industries at or above that threshold.
In modern SQL, you'd more often accomplish this with RANK() or DENSE_RANK() as window functions — covered in Window Functions: RANK, ROW_NUMBER, and LAG. But understanding the HAVING-based approach helps you reason about why window functions were invented and gives you a fallback when they're not available.
The distinction often isn't either/or. Many well-written queries use both WHERE and HAVING together, each doing what it's best at.
The rule of thumb:
WHEREHAVINGWHERE; if it describes the group, it's HAVINGHere's a query that uses both correctly, finding enterprise accounts in the financial services industry where the account's total subscription value exceeds $5,000/month:
SELECT
a.account_name,
a.industry,
COUNT(s.subscription_id) AS subscription_count,
SUM(s.monthly_value) AS total_monthly_value
FROM accounts a
JOIN subscriptions s ON a.account_id = s.account_id
WHERE
a.industry = 'Financial Services' -- row-level filter: column value
AND s.end_date IS NULL -- row-level filter: column value
AND s.plan_type = 'enterprise' -- row-level filter: column value
GROUP BY a.account_id, a.account_name, a.industry
HAVING SUM(s.monthly_value) > 5000 -- group-level filter: aggregate
ORDER BY total_monthly_value DESC;
The three WHERE conditions eliminate rows before grouping, making the grouping operation work with a smaller, cleaner dataset. The HAVING condition then trims the groups to only those meeting the revenue threshold. This is the optimal approach — push filtering as early as possible.
Tip
A common mistake is writing conditions in HAVING that could be in WHERE. For example, writing HAVING plan_type = 'enterprise' instead of WHERE plan_type = 'enterprise'. The HAVING version works, but it forces the database to group all plan types before discarding non-enterprise rows. WHERE eliminates those rows before grouping, which is almost always faster — especially on large tables.
Sometimes you want to both compute a conditional aggregate and display it, using the same logic in SELECT and HAVING. The natural instinct is to reference the alias from SELECT in HAVING, but as noted earlier, that's not reliably portable. The solution is to repeat the expression — or use a CTE to compute it once.
Here's an example using a CTE for clarity. For more on CTE patterns, see Common Table Expressions (CTEs) for Cleaner SQL.
Find sales teams where at least one rep achieved >$500K in annual contract value, and report team-level rollup stats:
WITH rep_performance AS (
SELECT
sr.sales_rep_id,
sr.rep_name,
sr.team,
SUM(s.monthly_value * 12) AS acv,
COUNT(DISTINCT s.account_id) AS accounts_managed,
SUM(CASE WHEN s.plan_type = 'enterprise' THEN 1 ELSE 0 END)
AS enterprise_deals
FROM sales_reps sr
JOIN subscriptions s ON sr.sales_rep_id = s.sales_rep_id
WHERE s.end_date IS NULL
GROUP BY sr.sales_rep_id, sr.rep_name, sr.team
)
SELECT
team,
COUNT(sales_rep_id) AS team_size,
SUM(acv) AS team_total_acv,
ROUND(AVG(acv), 0) AS avg_rep_acv,
MAX(acv) AS top_rep_acv,
SUM(enterprise_deals) AS team_enterprise_deals
FROM rep_performance
GROUP BY team
HAVING MAX(acv) >= 500000
ORDER BY team_total_acv DESC;
Breaking the query into layers like this — first compute rep-level metrics, then aggregate to team level with HAVING — is far more readable than nesting everything into one dense query. The CTE also lets you reference acv by name in the outer HAVING without repeating the full expression.
Let's put everything together in a single cohesive analytical project. You've been asked to build a churn risk summary for the customer success team. The output should identify industries where renewal health is deteriorating — specifically those that meet all of these criteria:
WITH renewal_summary AS (
SELECT
a.industry,
COUNT(DISTINCT a.account_id) AS accounts_with_renewals,
COUNT(r.renewal_id) AS total_renewal_events,
-- Count successful renewals
SUM(CASE WHEN r.renewed = TRUE THEN 1 ELSE 0 END) AS renewals_won,
-- Count churned renewals
SUM(CASE WHEN r.renewed = FALSE THEN 1 ELSE 0 END) AS renewals_lost,
-- Average original subscription value
AVG(s.monthly_value) AS avg_original_value,
-- Average value at renewal (only for those that renewed)
AVG(CASE WHEN r.renewed = TRUE THEN r.renewal_value END) AS avg_renewal_value,
-- Renewal rate as a decimal
ROUND(
1.0 * SUM(CASE WHEN r.renewed = TRUE THEN 1 ELSE 0 END)
/ NULLIF(COUNT(r.renewal_id), 0),
4
) AS renewal_rate
FROM accounts a
JOIN subscriptions s ON a.account_id = s.account_id
JOIN renewals r ON s.subscription_id = r.subscription_id
WHERE r.renewal_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY a.industry
)
SELECT
industry,
accounts_with_renewals,
total_renewal_events,
renewals_won,
renewals_lost,
ROUND(renewal_rate * 100, 1) AS renewal_rate_pct,
ROUND(avg_original_value, 2) AS avg_original_monthly_value,
ROUND(avg_renewal_value, 2) AS avg_renewal_value,
ROUND(avg_renewal_value - avg_original_value, 2)
AS value_delta
FROM renewal_summary
WHERE accounts_with_renewals >= 15 -- minimum volume threshold
HAVING
renewal_rate < 0.70 -- below 70% renewal rate
AND avg_renewal_value < avg_original_value -- value is declining
ORDER BY renewal_rate ASC;
Note
Notice that accounts_with_renewals >= 15 is in the outer WHERE clause here, not HAVING. That's because we moved the grouping work into the CTE — at the outer query level, accounts_with_renewals is a regular column from the CTE, not an aggregate. This is one of the great advantages of CTEs: they let you treat aggregate results as plain data in subsequent filtering.
This query gives the customer success team a prioritized list of at-risk industries, with all the context they need: volume of renewals, win/loss counts, rate, and whether customers are paying more or less upon renewal. The HAVING clause enforces the business-defined risk criteria, and the ORDER BY renewal_rate ASC puts the most alarming industries at the top.
Work through these exercises against your own database or set up the schema above with sample data.
Exercise 1 — Basic HAVING: Write a query that finds all sales reps who have closed more than 10 active subscriptions. Show the rep's name, number of subscriptions, and total monthly value.
Exercise 2 — Conditional aggregate in HAVING: Find all industries where more than 40% of subscriptions are on the "professional" plan. Show the industry name, total subscription count, professional plan count, and percentage.
Exercise 3 — Dynamic threshold: Find accounts (individual companies) whose total monthly subscription value exceeds the average total value per account across the entire database. Show account name, industry, and their total monthly value.
Exercise 4 — Multi-condition HAVING:
Write a query to identify sales teams (group by team) that have:
Show team name, rep count, total MRR, and average enterprise subscriptions per rep.
Exercise 5 — CTE + HAVING:
Refactor the Exercise 3 query using a CTE for readability. In the CTE, compute per-account totals. In the outer query, apply a HAVING (or WHERE, if appropriate after the refactor) to filter above the average.
-- ❌ Fails in most databases
SELECT
industry,
SUM(monthly_value) AS total_mrr
FROM ...
GROUP BY industry
HAVING total_mrr > 50000; -- alias not yet defined at HAVING stage
-- ✅ Repeat the expression
HAVING SUM(monthly_value) > 50000;
-- ❌ Runtime error
WHERE COUNT(subscription_id) > 5
-- ✅ Move it to HAVING
HAVING COUNT(subscription_id) > 5
-- ❌ Works, but slower — forces grouping of all industries first
HAVING industry = 'Financial Services'
-- ✅ Filter early with WHERE
WHERE industry = 'Financial Services'
If any group has a count of zero (unlikely but possible with outer joins), division will throw a runtime error. Always use NULLIF(denominator, 0).
-- ❌ Potential division by zero
HAVING SUM(renewed_count) / COUNT(renewal_id) > 0.7
-- ✅ Safe division
HAVING SUM(renewed_count) * 1.0 / NULLIF(COUNT(renewal_id), 0) > 0.7
A renewal rate of 100% on 2 renewals isn't meaningful. Always consider combining your rate-based HAVING filter with a minimum volume condition:
HAVING
COUNT(renewal_id) >= 10 -- minimum volume
AND SUM(CASE WHEN renewed THEN 1 ELSE 0 END) * 1.0
/ COUNT(renewal_id) > 0.80 -- rate threshold
HAVING without GROUP BY treats the entire result set as a single group. This is occasionally intentional (e.g., checking whether a table has any rows matching a condition), but usually a mistake:
-- This treats all rows as one group and returns either all rows or none
SELECT * FROM subscriptions
HAVING COUNT(*) > 100;
HAVING itself isn't inherently slow, but the patterns around it affect performance significantly.
Apply WHERE first. Every row eliminated by WHERE before grouping is a row the database doesn't need to process, sort, or aggregate. If you can express a condition on raw column values, put it in WHERE. Save HAVING for genuine aggregate conditions.
Index your GROUP BY columns. When GROUP BY runs on unindexed columns in large tables, the database has to sort or hash the full row set. Indexes on frequently grouped columns (like industry, region, plan_type) help enormously. See SQL Indexes Explained: How They Work and When to Create Them for the mechanics.
Subqueries in HAVING add cost. A non-correlated subquery in HAVING (like our dynamic threshold example) executes once and is generally fine. A correlated subquery executes once per group — on a table with 10,000 distinct groups, that's 10,000 subquery executions. CTEs or window functions are usually better alternatives in that case.
COUNT(DISTINCT ...) is expensive. COUNT(DISTINCT column) requires sorting or hashing to deduplicate. If you use it frequently in HAVING, check whether approximation functions (HLL, approx_count_distinct in some databases) are acceptable for your use case.
Tip
When a HAVING query feels slow, add EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) before the query to see the execution plan. Look for sequential scans on large tables where you expected an index scan, and large estimated row counts going into the GROUP BY operation. For more on reading execution plans, see SQL Query Optimization: Reading Execution Plans - Advanced Performance Analysis.
HAVING is the clause that brings aggregate filtering into reach. Once you internalize SQL's execution order — rows filtered by WHERE, then grouped, then groups filtered by HAVING — the whole thing clicks into place. The logic flows naturally: build your groups, then decide which groups are worth keeping.
The patterns you've learned here scale to real production analytics:
Where to go from here:
The natural next step is combining HAVING with more advanced GROUP BY techniques like ROLLUP and CUBE — covered in Master SQL Aggregate Functions: Advanced GROUP BY, HAVING, and Performance Optimization.
If you found the conditional aggregation patterns compelling, go deeper with Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice — it covers multi-dimensional pivoting techniques that build directly on what you've practiced here.
And once you're filtering groups confidently with HAVING, the next frontier is filtering groups with window functions — where you can rank, compare, and partition without changing the output row structure. That's where Window Functions: RANK, ROW_NUMBER, and LAG picks up.