What does HAVING do in SQL?
HAVING filters groups after rows have been grouped and aggregate values are available. WHERE filters input rows before grouping. Use HAVING for conditions such as groups with more than ten orders or total revenue above a threshold.
SELECT customer_id,
COUNT(*) AS order_count,
SUM(order_total) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(order_total) > 10000;WHERE defines which orders contribute to every metric; HAVING decides which customer groups remain after revenue is calculated. Moving the status predicate into HAVING can change the metric, fail portability, or force unnecessary work.
Follow the logical row-to-group pipeline
A useful model is FROM and JOIN establish rows, WHERE removes ineligible rows, GROUP BY forms groups, aggregates summarize them, HAVING removes groups, SELECT projects results, and final ORDER BY sorts output. Optimizers may physically reorder safe operations, but SQL meaning follows these boundaries.
Write the population question and group question separately. “Completed orders in 2026” is row eligibility. “Customers whose completed 2026 revenue exceeds 10,000” is group eligibility. This separation makes reviews and predicate placement easier.
Move predicates only when semantics are equivalent
A predicate on a grouping key may often be applied before grouping because every row in a group shares that key. A predicate on SUM, COUNT, AVG, MIN, or MAX requires group results. Conditions on non-grouped detail columns belong in WHERE or a deliberate conditional aggregate, not an ambiguous HAVING shortcut.
| Question | Stage | Reason |
|---|---|---|
| Orders created this year | WHERE | Controls input population |
| Customers with five orders | HAVING | Needs group count |
| Only one region | WHERE | Safe grouping-key reduction |
Filter counts, totals, averages, and distinct entities correctly
HAVING COUNT(*) > 1 finds groups with multiple rows, often useful for duplicate-key diagnosis. HAVING COUNT(column) = 0 finds groups with no non-null values, not empty groups. HAVING COUNT(DISTINCT user_id) >= 100 filters by unique users. Define what is being counted before selecting the expression.
For averages, HAVING applies after null inputs are ignored. A group with no non-null values has AVG NULL, and comparisons with it are not true. For weighted metrics, calculate numerator and denominator explicitly; average-of-averages can produce wrong eligibility.
Use conditional aggregation when several populations share one group
SELECT customer_id,
COUNT(*) AS all_orders,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders
FROM orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) >= 5;This keeps all orders in the all-orders metric while filtering groups by completed count. Moving status to WHERE would make both metrics completed-only. Calculate named metrics in a CTE when expressions are long or reused, then filter the named result outside for clarity and alias portability.
Avoid relying on engine-specific alias rules
Some databases allow a SELECT alias in HAVING; others do not because logical name resolution differs. Portable SQL repeats the aggregate expression or places aggregation in a CTE or derived table, where the metric becomes an ordinary named column in the outer query.
Also verify HAVING without GROUP BY. Many engines treat the complete qualifying input as one implicit group, which can be useful for assertion-like checks. If portability or reader expectations matter, make the single-group intent explicit in documentation or a named stage.
Validate group metrics before applying HAVING
HAVING can faithfully filter a wrong aggregate. A one-to-many join may duplicate amounts, pushing groups above a threshold they never truly reached. Measure row expansion and distinct fact keys before grouping. Aggregate independent child branches at their natural grain before combining when final metrics remain at parent grain.
Do not use HAVING or DISTINCT to hide duplicated projections. Repair the relationship and then revalidate threshold membership with a direct control query.
Push safe row predicates and inspect aggregate cost
Moving a true row predicate from HAVING to WHERE can reduce input before hash or sort aggregation. Optimizers may do this automatically for grouping-key conditions, but clear SQL makes intent reviewable. Aggregate thresholds themselves cannot be evaluated before grouping without an equivalent transformation.
| Signal | Cause | Check |
|---|---|---|
| Large aggregate input | Late row filtering | Predicate stage and semantics |
| Hash spill | More groups or width than estimated | Actual groups, width, memory |
| Slow DISTINCT | High-cardinality state | Metric need and input grain |
Validate rows, groups, and threshold boundaries
- Write row eligibility and group eligibility separately.
- Test groups below, equal to, and above every threshold.
- Include empty, all-null, one-row, duplicate, and high-cardinality groups.
- Measure join expansion before aggregation.
- Compare pushed and unpushed predicates for exact equivalence.
- Reconcile counts, sums, numerators, and denominators.
- Inspect actual groups, memory, sorts, spills, and estimates.
- Preserve boundary fixtures as regression tests.
Diagnose groups that cross a threshold after a join
Suppose VIP customers require revenue above 50,000, but several small customers qualify after a promotion join. Each order matches multiple promotion rows, so SUM doubles before HAVING. Compare revenue by distinct order key, profile matches per order, and aggregate promotions before joining. HAVING is not the cause; it exposed a metric already inflated upstream.
Inspect the complete aggregation query
Paste a sanitized complete statement into the InfiniSynapse SQL Complexity Checker to review joins, row filters, grouping, HAVING, conditional aggregates, windows, and nesting together. Then validate metrics with source data and the actual plan.
Open SQL Complexity CheckerRemove credentials, secrets, personal data, and sensitive literals.HAVING clause frequently asked questions
It filters groups after aggregation.
WHERE filters rows before grouping and therefore changes aggregate input.
Many engines allow one implicit group; verify portability.
Support differs; a CTE or repeated expression is more portable.
Move true row predicates to WHERE and inspect the aggregate plan without changing semantics.
Official HAVING documentation
Design HAVING rules as governed metric thresholds
A production HAVING rule is usually a business decision disguised as a predicate. “Customers with revenue above 50,000” requires a currency, time zone, reporting period, order status, refund treatment, tax policy, and customer identity rule. Store these definitions beside the SQL. When the threshold changes, version the rule or effective date so historical membership can be reproduced rather than recalculated under today's policy.
Time basis is especially easy to mix. Row eligibility may use order completion time, while a payment metric uses settlement time and a refund metric uses refund approval time. Combining all three after one shared WHERE date filter can make at least one metric wrong. Build separate metric stages when populations differ, then join them at a declared business grain before applying final group eligibility.
Empty groups need deliberate construction. GROUP BY cannot return a group that has no input rows. If every store must appear even with no sales, start from the store population and left join eligible sales. Count a non-null sale key, not COUNT(*), and decide whether missing SUM becomes NULL or zero. Then HAVING can retain or exclude zero-activity stores according to the actual requirement.
Conditional metrics deserve named numerators and denominators. A conversion-rate threshold might require converted users divided by eligible users, not converted events divided by all events. Calculate both components visibly, protect the zero denominator, expose the resulting rate, and apply HAVING to that exact definition. If the rate is rounded for display, choose whether qualification uses raw or rounded precision and test values around the boundary.
Monitor threshold membership, not only query runtime. Track input rows, group count before HAVING, groups retained, distribution near the threshold, null metrics, largest groups, and join expansion. A sudden membership jump can result from a real business event, duplicate ingestion, changed currency conversion, or predicate movement. Keeping boundary samples and source totals makes those mechanisms distinguishable.
Finally, review privacy and fairness when group thresholds trigger action. Minimum-count rules can reduce disclosure risk but do not automatically guarantee anonymity. Eligibility thresholds may disadvantage small or newly formed groups. Document approved purpose, access, retention, and human override where decisions affect people. Query correctness includes responsible interpretation of the group metric.
Test thresholds as boundaries, not ordinary values
Create groups whose metric is just below, exactly equal to, and just above the threshold. Include decimal precision, currency conversion, negative adjustments, late corrections, and a group whose value changes after a duplicated join. Confirm whether the rule is strictly greater than, greater than or equal, or based on a rounded presentation value. A one-character operator change can alter membership materially.
For rolling periods, freeze an as-of time and time zone in tests. A group can cross the threshold when an old row expires even if no new event arrives. For cumulative lifetime rules, define how merged identities, deleted accounts, reversals, and historical currency rates affect the total. Preserve the row set and aggregate components that explain each boundary decision.
Keep group membership explainable
For every action-triggering HAVING rule, store the qualifying group key, metric components, threshold version, source cutoff, and exclusion reason. A reviewer should be able to rebuild one included and one excluded group from source rows. This evidence shortens disputes, catches silent predicate movement, and makes policy changes auditable.
Verify the gate one last time
Before release, list retained and rejected groups around the threshold, confirm their metric components, and preserve the evidence with the rule version.