HAVING Clause in SQL
Introduction
The SQL HAVING clause filters groups after GROUP BY and aggregate calculations. It answers questions that cannot be decided by examining one source row at a time, such as which departments have more than ten employees, which customers spent over 10,000, or which API endpoints have an average response time above a service threshold.
SELECT department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;
This query first forms one group per department, calculates each department's average salary, and then retains only averages greater than 70,000. The easiest distinction to remember is that WHERE filters individual rows before grouping, while HAVING filters aggregated groups.
Why HAVING Exists
Suppose IT salaries are 80,000 and 100,000, HR salaries are 50,000 and 60,000, and Finance salaries are 90,000 and 100,000. Grouping and averaging produces 90,000 for IT, 55,000 for HR, and 95,000 for Finance. A requirement to show departments whose average exceeds 70,000 is a decision about those completed group values, not about any single employee.
| department | average_salary | passes HAVING? |
|---|---|---|
| IT | 90000 | Yes |
| HR | 55000 | No |
| Finance | 95000 | Yes |
WHERE cannot directly test that average because the average does not exist at the logical row-filtering stage. HAVING exists to make conditions on group-level calculations explicit.
Basic Syntax
SELECT grouping_expression,
aggregate_function(measure_expression)
FROM table_name
WHERE row_condition
GROUP BY grouping_expression
HAVING aggregate_condition
ORDER BY grouping_expression;
WHERE and ORDER BY are optional, but the order shown reflects their written placement. A useful reading is: choose eligible rows, form groups, calculate aggregates, keep qualifying groups, and present the result.
The HAVING Mental Model
Detailed rows
|
v
WHERE filters rows
|
v
GROUP BY forms groups
|
v
Aggregate functions calculate metrics
|
v
HAVING filters groups
|
v
SELECT and ORDER BY present results
The database optimizer can physically execute operations differently while preserving equivalent semantics. This model is about logical meaning, which is what prevents row conditions and group conditions from being confused.
WHERE vs HAVING
| Feature | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups or aggregate results |
| Logical stage | Before grouping | After grouping |
| Typical condition | salary > 50000 | AVG(salary) > 50000 |
| Primary purpose | Define eligible source data | Define eligible summaries |
WHERE salary > 50000 removes individual employees whose salary does not pass the threshold before averages are calculated. HAVING AVG(salary) > 50000 calculates an average from all otherwise eligible employees and removes whole departments afterward. These queries answer different questions.
HAVING with COUNT()
COUNT is one of the most common HAVING aggregates. It can keep categories with enough observations or identify values that occur more than once.
SELECT department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;
The boundary matters. >= 5 includes exactly five, while > 5 does not. Tests should include groups immediately below, at, and above the threshold.
HAVING with SUM()
SUM-based conditions retain groups whose total reaches a business threshold, such as customer spending, regional revenue, account movement, or inventory quantity.
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
HAVING SUM(total_amount) > 10000;
The WHERE clause ensures that pending and cancelled orders never contribute. HAVING then compares each completed-order total with 10,000.
HAVING with AVG()
Average thresholds are useful for quality, performance, pricing, and compensation reports, but averages need enough observations to be meaningful.
SELECT endpoint,
COUNT(*) AS request_count,
AVG(response_time_ms) AS average_response_ms
FROM api_requests
WHERE observed_at >= :period_start
GROUP BY endpoint
HAVING COUNT(*) >= 100
AND AVG(response_time_ms) > 500;
The count condition avoids highlighting an endpoint as generally slow based on one or two requests. Statistical decisions may require percentiles rather than averages, but the principle of minimum sample size remains valuable.
HAVING with MIN() and MAX()
MIN can keep groups whose smallest value passes a floor; MAX can keep groups containing a value above a ceiling. If salaries are complete, HAVING MIN(salary) >= 50000 means every non-NULL salary in the group is at least 50,000. If NULL salaries exist, that conclusion needs qualification because MIN ignores them.
SELECT department,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department
HAVING MIN(salary) >= 50000
AND MAX(salary) <= 150000;
This retains departments whose observed non-NULL salary range stays within both boundaries.
Using WHERE, GROUP BY, and HAVING Together
SELECT department,
COUNT(*) AS active_employee_count,
AVG(salary) AS active_average_salary
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING COUNT(*) >= 5
AND AVG(salary) > 70000;
Read this in stages: keep active employees, group them by department, calculate active employee metrics, and retain departments with at least five active employees and an active average above 70,000. Clear aliases explain the population even though portable HAVING expressions repeat the aggregates.
Logical Query Processing Order
A simplified logical order is FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and row limiting. Because SELECT logically follows HAVING, using a SELECT alias in HAVING is database-dependent.
This order also explains why aggregate functions normally cannot appear in WHERE: groups do not yet exist. The correct clause for COUNT(*) > 5 is HAVING, or an outer WHERE if the aggregation is performed in a subquery or CTE first.
Multiple HAVING Conditions
Conditions can be combined with AND and OR, but parentheses should make mixed logic explicit. A department can be required to meet both a size and salary rule:
HAVING COUNT(*) >= 10
AND AVG(salary) > 70000
Alternatively, a risk report might keep groups with unusually high volume or unusually high value. Write the exact business rule and test groups that satisfy each branch separately. Prefer direct comparisons such as COUNT(*) >= 5 over harder-to-read equivalents like NOT COUNT(*) < 5.
BETWEEN and IN with HAVING
BETWEEN can keep aggregate values within an inclusive range, while IN can compare an aggregate with a discrete set. These are less common than ordinary comparisons but remain valid where they communicate the rule clearly.
SELECT department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) BETWEEN 60000 AND 90000;
Because BETWEEN includes both endpoints, boundary tests are essential. For monetary values, verify scale and rounding before comparing a calculated average.
Prefer WHERE for Pre-Aggregation Filters
If a condition correctly excludes rows before grouping, place it in WHERE. This communicates intent and often reduces work. For example, a completed-order revenue report should use WHERE status = 'Completed', not group all statuses and rely on a non-aggregate HAVING condition.
There is an important exception: if the report needs all orders in each customer's total but keeps only customers who have at least one completed order, filtering completed orders in WHERE would remove data needed by the total. That requirement calls for conditional aggregation in HAVING.
HAVING with COUNT(DISTINCT)
Distinct aggregate conditions can keep groups containing enough unique values. The following query finds customers who bought from at least five distinct product categories:
SELECT customer_id,
COUNT(DISTINCT category_id) AS category_count
FROM order_items
GROUP BY customer_id
HAVING COUNT(DISTINCT category_id) >= 5;
NULL category IDs are not counted by COUNT DISTINCT. Exact distinct counts can be expensive on large datasets, so inspect execution plans and use documented approximate methods only when the accuracy requirement permits them.
Finding Duplicate Values
Duplicate detection is the classic GROUP BY and HAVING pattern. Group by the value expected to be unique, count occurrences, and keep groups above one.
SELECT email,
COUNT(*) AS occurrences
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
The WHERE condition excludes the NULL group when missing emails are not considered duplicates. If case and whitespace should be ignored, normalize consistently or, preferably, store a constrained canonical value.
Duplicate Composite Keys
When uniqueness is defined by several columns, every component belongs in the group key.
SELECT first_name,
last_name,
birth_date,
COUNT(*) AS occurrences
FROM customers
GROUP BY first_name, last_name, birth_date
HAVING COUNT(*) > 1;
This identifies duplicate combinations, not necessarily duplicate people. Real identity matching may require normalization, trusted identifiers, and domain-specific review.
Customers with More Than or Exactly One Order
SELECT customer_id,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
Changing the condition to COUNT(*) = 1 finds customers represented by exactly one order row. Ensure the source grain is one row per order; joining order items first could count items instead.
Finding Customers with No Orders
Zero-member groups do not arise from the child table alone because no order row exists to create them. Start with customers, preserve them through a LEFT JOIN, group, and test the count of a non-NULL order key.
SELECT c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) = 0;
COUNT(*) would count the preserved null-extended customer row and return one, so it would not identify zero matches correctly.
Conditional Aggregation in HAVING
Conditional aggregation tests properties of a group without removing rows needed by other calculations. The next query returns customers who have at least one completed order while still calculating their total order count across all statuses.
SELECT customer_id,
COUNT(*) AS all_order_count
FROM orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) > 0;
Moving status = 'Completed' to WHERE would change all_order_count into a completed-order count. Conditional aggregation is necessary because group eligibility and displayed population differ.
Groups Containing Several Required States
A group can be required to contain both completed and cancelled records by using separate conditional aggregates.
SELECT customer_id,
COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) > 0;
An alternative is COUNT(DISTINCT CASE WHEN status IN (...) THEN status END) = 2. Choose the form that makes the required states obvious and behaves correctly with NULL.
HAVING with Ratios and Percentages
Ratios often combine conditional counts with a total count. Casting or multiplying by a decimal prevents integer division in databases where integer divided by integer truncates.
SELECT course_id,
COUNT(*) AS attempt_count,
100.0 * SUM(CASE WHEN result = 'Pass' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS pass_percentage
FROM exam_attempts
GROUP BY course_id
HAVING COUNT(*) >= 20
AND 100.0 * SUM(CASE WHEN result = 'Pass' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) < 60;
The minimum attempt count prevents unstable percentages based on tiny samples. NULLIF documents protection from division by zero, although a normal nonempty group has at least one row.
HAVING and NULL
Aggregate NULL behavior directly affects HAVING. COUNT(column) counts non-NULL values; SUM, AVG, MIN, and MAX ignore NULL but return NULL when no non-NULL input remains. A comparison such as SUM(bonus) > 0 is unknown for an all-NULL group and therefore does not retain it.
Do not automatically write COALESCE(SUM(bonus), 0). That is correct only if “no known bonus values” should be treated as a zero bonus total.
Finding Groups with Missing Values
Comparing row count with non-NULL count provides concise data-quality checks.
-- At least one missing email
SELECT department, COUNT(*) AS employees
FROM employees
GROUP BY department
HAVING COUNT(email) < COUNT(*);
-- No missing emails
SELECT department, COUNT(*) AS employees
FROM employees
GROUP BY department
HAVING COUNT(email) = COUNT(*);
HAVING COUNT(email) = 0 finds groups where every email is NULL, not groups with no rows. The group exists because other rows are present.
HAVING with Dates
MIN and MAX dates can filter groups by first activity, latest activity, or activity span. For example, inactive customers can be defined by their last completed order:
SELECT customer_id,
MAX(order_date) AS last_order_date
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
HAVING MAX(order_date) < :inactive_before;
This query cannot return customers who never ordered because they have no order group. Include them with a LEFT JOIN and explicit NULL logic if “never ordered” belongs in the inactive population.
Filtering by Activity Span
A group can be retained when the duration between its first and latest events exceeds a threshold. Date-difference syntax varies by database.
SELECT customer_id,
MIN(order_date) AS first_order_date,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) - MIN(order_date) > INTERVAL '365 days';
Clarify whether time-of-day, timezone, and incomplete dates matter. Calendar-year membership and a duration of 365 days are not always the same rule.
Date Range Filtering Before HAVING
A report period normally belongs in WHERE so only events in that period contribute to the group metrics.
SELECT customer_id,
COUNT(*) AS yearly_orders,
SUM(total_amount) AS yearly_spend
FROM orders
WHERE order_date >= :year_start
AND order_date < :next_year_start
GROUP BY customer_id
HAVING COUNT(*) >= 10;
Half-open ranges avoid time-of-day errors and prevent overlap between adjacent periods. HAVING then applies to the period-specific count.
Multiple Grouping Columns Change HAVING Meaning
HAVING always tests the groups produced by the exact GROUP BY list. Grouping by customer tests one group per customer; grouping by customer and month tests one group per customer-month. The same HAVING COUNT(*) > 5 therefore means either more than five orders overall or more than five orders in a particular month.
State the output grain before interpreting a HAVING result. Adding a dimension to satisfy a select-list requirement can completely change which groups pass.
HAVING Without GROUP BY
Many databases allow HAVING without an explicit GROUP BY when the query contains aggregates. The entire filtered result is treated as one group.
SELECT COUNT(*) AS employee_count
FROM employees
HAVING COUNT(*) > 100;
The query returns one row only if the table count exceeds 100; otherwise it returns no rows. Support for non-aggregate HAVING behavior varies, so avoid using HAVING as a substitute for WHERE.
Alias Portability
Some databases permit a SELECT alias in HAVING, while others do not because HAVING is logically evaluated before SELECT. Repeating a short aggregate is portable:
SELECT department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;
For complex expressions, calculate the aggregate in a CTE and filter its named result in an outer WHERE. This avoids duplication and separates aggregation from selection.
CTE Alternative for Complex Conditions
WITH customer_summary AS (
SELECT customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent,
AVG(total_amount) AS average_order
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
)
SELECT customer_id,
order_count,
total_spent,
average_order
FROM customer_summary
WHERE order_count >= 5
AND total_spent > 10000;
The outer WHERE filters rows of the already aggregated CTE. This is semantically equivalent to HAVING for those conditions and may be easier to extend, test, and join to other summaries.
Comparing Groups with an Overall Aggregate
A group condition can compare its aggregate with a scalar subquery. The following keeps departments whose average exceeds the company-wide average:
SELECT department,
AVG(salary) AS department_average
FROM employees
GROUP BY department
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);
Ensure both sides use compatible populations. If the outer query filters to active employees but the subquery averages all employees, the comparison may be intentional or a subtle defect.
HAVING vs Window Filtering
HAVING filters collapsed group rows. It cannot preserve individual employees while filtering departments by department average. A window function computes the group metric beside each detail row, and an outer query filters that result.
WITH employee_metrics AS (
SELECT e.*,
AVG(salary) OVER (PARTITION BY department)
AS department_average
FROM employees e
)
SELECT *
FROM employee_metrics
WHERE department_average > 70000;
Choose HAVING when the desired output is one row per group. Choose a window plus outer filter when detail rows must remain.
HAVING After JOIN
Joins logically happen before grouping and HAVING. A one-to-many join can be correct, but joining several many-side tables can multiply rows and cause groups to pass thresholds incorrectly. One order with three items and two payments can become six joined rows.
Count the correct entity, often with a stable key, and pre-aggregate child tables when several measures have different grains. Do not use COUNT(DISTINCT ...) or SUM(DISTINCT ...) blindly as a repair; repeated legitimate values can be lost.
Safe Pre-Aggregation Before HAVING
WITH order_totals AS (
SELECT o.customer_id,
o.order_id,
SUM(i.quantity * i.unit_price) AS order_total
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id, o.order_id
)
SELECT customer_id,
COUNT(*) AS order_count,
SUM(order_total) AS customer_total
FROM order_totals
GROUP BY customer_id
HAVING SUM(order_total) > 10000;
The first level creates one row per order; the second creates one row per customer. Explicit intermediate grain prevents item rows from being mistaken for orders.
Percentage of Overall Total
Groups can be filtered by their contribution to an overall total. A CTE usually expresses this more clearly than repeating nested calculations.
WITH regional_sales AS (
SELECT region,
SUM(amount) AS regional_total
FROM sales
GROUP BY region
),
overall AS (
SELECT SUM(regional_total) AS grand_total
FROM regional_sales
)
SELECT r.region,
r.regional_total,
100.0 * r.regional_total / NULLIF(o.grand_total, 0)
AS percentage_of_total
FROM regional_sales r
CROSS JOIN overall o
WHERE 100.0 * r.regional_total / NULLIF(o.grand_total, 0) >= 10;
This returns regions contributing at least ten percent. Negative amounts, refunds, and a zero grand total require explicit business rules.
Negative Values, Refunds, and Net Totals
A SUM condition can behave unexpectedly when groups contain both positive and negative values. In an account ledger, deposits may be positive and withdrawals negative. HAVING SUM(amount) > 1000 tests the net movement, not total money entering the account and not total transaction volume. A customer with 10,000 in deposits and 9,500 in withdrawals has a net of only 500.
If the report needs separate inflow and outflow thresholds, calculate them explicitly:
SELECT account_id,
SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS inflow,
SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) AS outflow,
SUM(amount) AS net_change
FROM transactions
GROUP BY account_id
HAVING SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) > 10000
OR SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) > 10000;
Refunds create the same issue in sales reporting. Decide whether revenue means gross charges, net charges after refunds, or recognized revenue under an accounting rule. HAVING can enforce any of these definitions, but it cannot choose the definition for the business.
Zero Is Different from NULL
HAVING SUM(amount) = 0 can identify a group whose positive and negative values cancel each other. It does not necessarily identify a group with no transactions, and an all-NULL group usually has a NULL sum rather than zero. These cases carry different meanings.
If an outer join must retain entities with no matching rows, test the matching key count separately. If all-NULL measures should be reported as unknown, do not use COALESCE to convert them to zero merely to simplify the condition.
Filtering by an Aggregate Not Shown
An aggregate used in HAVING does not always need to appear in the select list. A report may show department payroll while requiring at least five employees:
SELECT department,
SUM(salary) AS payroll
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;
The query is valid because COUNT is well-defined for each group. For maintainability, consider displaying the threshold metric when users need to understand why a row qualified. Hidden eligibility logic should at least be documented in the report specification.
Precision, Rounding, and Threshold Comparisons
Monetary and percentage thresholds require consistent numeric types. Comparing an unrounded aggregate with a displayed rounded value can confuse users: a displayed average of 70.00 may fail HAVING AVG(value) >= 70 if the display and condition use different conversions, or a value shown as 69.995 may round to 70.00 while still failing.
Define whether the rule applies to the exact stored calculation or the rounded business value, and implement the same rule in validation. Prefer exact decimal types for money. Floating-point measurements may require domain-specific tolerances rather than direct equality tests.
Performance Considerations
HAVING is evaluated after grouping in the logical model, so the database may still need to process all eligible rows and build aggregate state before it knows which groups survive. A very selective HAVING condition does not necessarily make the earlier aggregation cheap.
Push valid row conditions into WHERE, keep group keys no more detailed than required, and avoid unnecessary distinct aggregates. Indexes on selective filter columns and join keys can reduce input work; useful ordering or covering may also help depending on the engine. Inspect execution plans with representative volumes rather than assuming HAVING itself is the bottleneck.
For frequent large summaries, materialized views or summary tables can precompute group metrics. They introduce refresh, late-arrival, correction, and deletion responsibilities, so freshness must be visible to report consumers.
Security and Privacy
HAVING thresholds are sometimes used to suppress sensitive small groups, but they are not a complete privacy system. Overlapping queries may allow users to infer hidden values. Tenant and authorization predicates must be applied before grouping so data from unauthorized rows never contributes to an aggregate.
For regulated demographic, health, or employee reports, use reviewed suppression, rounding, minimum-population, and access policies rather than relying on one count condition.
Transaction and Snapshot Consistency
Concurrent inserts and updates can change which groups pass a threshold. Different dashboard queries may observe different moments under some isolation levels. Reconciled reporting should use a consistent snapshot, explicit cutoff, or prepared dataset, and exported reports should identify the as-of time.
Real-World Dashboard Example
SELECT region,
COUNT(*) AS completed_orders,
COUNT(DISTINCT customer_id) AS customers,
SUM(total_amount) AS revenue,
AVG(total_amount) AS average_order
FROM orders
WHERE status = 'Completed'
AND order_date >= :period_start
AND order_date < :period_end
GROUP BY region
HAVING COUNT(*) >= 100
AND SUM(total_amount) >= 50000
ORDER BY revenue DESC, region;
This query defines a period-specific completed-order population, summarizes it by region, retains regions with sufficient activity and revenue, and then orders the surviving groups. The count and revenue thresholds refer to exactly the same rows.
Testing HAVING Queries
Test empty input, one group, several groups, one-row groups, groups exactly on thresholds, NULL group keys, all-NULL measures, zero and negative totals, duplicate child rows, and outer-join groups with no matches. Conditional aggregation needs groups satisfying each combination of required states.
For dates, include cutoff equality, month and year boundaries, timezones, and daylight-saving transitions. For ratios, test zero denominators, integer division, rounding, and minimum sample size. Reconcile grouped results against independently known totals and inspect complete rows when a group unexpectedly passes.
Common Mistakes
- Putting an aggregate condition in WHERE.
- Using HAVING for a row condition that belongs in WHERE.
- Assuming row salary and average group salary mean the same thing.
- Filtering rows before aggregation when all rows were needed for displayed metrics.
- Testing the wrong grouping grain.
- Using
COUNT(*)after a LEFT JOIN to detect child matches. - Ignoring row multiplication caused by joins.
- Forgetting that aggregates usually ignore NULL values.
- Assuming SELECT aliases work in HAVING on every database.
- Believing HAVING controls output order.
- Using a ratio without handling integer division or zero.
- Drawing conclusions from groups with tiny sample sizes.
Production Checklist
- Define what one grouped row represents.
- Separate row eligibility from group eligibility.
- Use WHERE for valid pre-aggregation filters.
- Use HAVING for conditions on aggregate results.
- Choose COUNT star or COUNT column deliberately.
- Document NULL, zero, negative, and all-NULL behavior.
- Verify join cardinality before applying thresholds.
- Pre-aggregate different fact grains when needed.
- Protect ratios from integer division and zero denominators.
- Use explicit period boundaries and timezones.
- Add ORDER BY when final order matters.
- Test threshold boundaries and representative data volume.
Interview Questions and Answers
What is HAVING in SQL?
HAVING filters groups after GROUP BY and aggregate calculation. It is commonly used with COUNT, SUM, AVG, MIN, and MAX.
What is the difference between WHERE and HAVING?
WHERE filters source rows before grouping. HAVING filters aggregated groups after grouping.
Can aggregate functions be used in WHERE?
Not directly in the same query level because groups do not yet exist at the logical WHERE stage. Use HAVING or aggregate in a subquery and filter with an outer WHERE.
Can WHERE and HAVING appear together?
Yes. WHERE defines the rows that enter groups, and HAVING defines which completed groups remain.
Can HAVING be used without GROUP BY?
Many databases allow it for an aggregate query, treating the complete filtered result as one group. Non-aggregate behavior is database-specific and should not replace WHERE.
How do you find duplicate emails?
Group by email, exclude NULL if appropriate, and use HAVING COUNT(*) > 1.
Why can HAVING return the wrong result after a join?
The join may multiply rows before grouping. Count the correct entity and pre-aggregate child tables to the required grain.
Complete Pattern
SELECT group_dimension,
COUNT(*) AS row_count,
SUM(measure) AS total_measure,
AVG(measure) AS average_measure
FROM source
WHERE eligible_row_condition
GROUP BY group_dimension
HAVING COUNT(*) >= minimum_group_size
AND SUM(measure) > threshold
ORDER BY total_measure DESC;
The dimension determines the grain, WHERE determines the population, aggregate functions calculate the metrics, HAVING determines group eligibility, and ORDER BY determines presentation.
Final Mental Model
WHERE asks: Should this individual row participate?
GROUP BY asks: Which group owns the participating row?
AGGREGATES ask: What metric describes each group?
HAVING asks: Should this completed group remain?
ORDER BY asks: How should surviving groups be displayed?
Keeping these questions separate makes even complex reporting queries easier to review. When one query becomes difficult to explain, prepare data in a CTE, assign an explicit grain to each stage, and filter the named summary in an outer query.
Key Takeaway
HAVING is the group-level filter of SQL. It is appropriate when eligibility depends on an aggregate result, while WHERE remains the correct place for conditions on individual source rows.
RELIABLE HAVING RESULT = CORRECT SOURCE POPULATION
+ EXPLICIT GROUP GRAIN
+ VALID AGGREGATE
+ CORRECT THRESHOLD
+ SAFE JOIN CARDINALITY
+ DEFINED NULL RULES
+ VERIFIED BOUNDARIES
Before writing HAVING, state what one group represents and why that complete group should pass or fail. That definition prevents most semantic errors.