Aggregate Filtering in SQL
Introduction
Aggregate filtering means retaining or rejecting data based on summary values produced by functions such as COUNT(), SUM(), AVG(), MIN(), and MAX(). A business question such as “show departments with more than ten employees” depends on a group count, while “show customers spending over 10,000” depends on a group sum.
SELECT department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
HAVING is the direct SQL clause for filtering grouped aggregate results, but aggregate filtering is broader than one clause. Reliable solutions may also use WHERE, conditional aggregation, CTEs, derived tables, correlated subqueries, EXISTS, or window functions. The right form depends on which rows should participate, what one group represents, whether detail rows must remain, and whether multiple aggregation levels are required.
What Is an Aggregate?
An aggregate function consumes values from several rows and returns a summary. If IT salaries are 70,000 and 80,000, AVG(salary) returns 75,000 for the IT group. The individual salary rows are inputs; the average is a new group-level value.
| Aggregate | Question answered | Important behavior |
|---|---|---|
COUNT(*) | How many rows? | Counts rows |
COUNT(column) | How many known values? | Ignores NULL |
SUM() | What is the total? | Usually ignores NULL |
AVG() | What is the mean? | Uses non-NULL values |
MIN() | What is smallest or earliest? | Returns one extreme |
MAX() | What is largest or latest? | Returns one extreme |
Filtering an aggregate means applying a condition to one of these calculated values after defining its input population and grain.
Basic Aggregate Filtering Pattern
SELECT grouping_expression,
aggregate_function(measure) AS metric
FROM source
WHERE row_condition
GROUP BY grouping_expression
HAVING aggregate_function(measure) comparison threshold;
The grouping expression defines one output group. WHERE determines which source rows enter those groups. The aggregate calculates a metric for each group, and HAVING decides which groups remain.
Why WHERE Cannot Normally Filter Aggregate Results
WHERE is logically evaluated before groups and their aggregates exist. Consequently, this form is invalid at the same query level:
-- Incorrect
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 70000
GROUP BY department;
The direct correction is HAVING AVG(salary) > 70000. Another valid design is to calculate department averages in a CTE and filter the resulting rows with an outer WHERE.
Logical Processing Order
FROM and JOIN
WHERE
GROUP BY
Aggregate calculations
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT or FETCH
This simplified logical order explains why WHERE changes aggregate inputs, HAVING filters completed groups, SELECT aliases are not portable in HAVING, and joins can multiply rows before calculations begin.
Row Filtering vs Aggregate Filtering
WHERE salary > 50000 removes individual employees whose salaries do not pass. HAVING AVG(salary) > 50000 retains departments whose average passes. These are different questions even when they use the same number.
Suppose IT salaries are 40,000 and 80,000. Filtering rows first leaves only 80,000, so the IT average becomes 80,000. Averaging all rows first produces 60,000 and then tests that result. A query should state whether the threshold applies to each observation or to the group metric.
Three Filtering Levels
Complex analytical queries often have three distinct filtering levels. WHERE filters source rows, HAVING filters collapsed groups, and an outer WHERE or QUALIFY-style feature filters rows after a subquery or window calculation. Confusing these levels changes both result grain and metric meaning.
Before choosing syntax, identify the value being tested: a stored row value, a grouped aggregate, a nested aggregate, or a window metric attached to detail rows.
Filtering with COUNT()
SELECT department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;
This asks which department groups contain at least five input rows. It means five employees only when one input row represents one employee. If a prior join duplicates employees, COUNT star measures joined rows instead. Grain is part of every aggregate's business meaning.
Filtering with SUM()
SELECT customer_id,
SUM(total_amount) AS completed_spend
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
HAVING SUM(total_amount) > 10000;
The status filter defines the rows contributing to spending. Refunds, discounts, tax, shipping, and currency conversion must be incorporated according to the definition of spend. A syntactically correct SUM can still represent the wrong measure.
Filtering with AVG()
Average filtering is common in product ratings and performance monitoring. Minimum sample size should usually accompany the average so one observation does not produce an unstable conclusion.
SELECT product_id,
COUNT(*) AS review_count,
AVG(rating) AS average_rating
FROM reviews
GROUP BY product_id
HAVING COUNT(*) >= 100
AND AVG(rating) >= 4.5;
The average ignores NULL ratings, while COUNT star includes their rows. If only rated reviews should determine sample size, use COUNT(rating).
Filtering with MIN() and MAX()
MIN and MAX can test group ranges or extreme dates. The following retains departments whose known salaries all fall within a stated range:
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;
Because both aggregates ignore NULL, this does not prove every employee has a salary. Add COUNT(salary) = COUNT(*) when completeness is required.
Multiple Aggregate Conditions
AND requires every condition, while OR requires at least one. Parenthesize mixed logic and test each branch separately.
HAVING COUNT(*) >= 20
AND (AVG(response_time_ms) > 500
OR MAX(response_time_ms) > 5000)
This keeps sufficiently active endpoints whose average is slow or whose worst observation is extreme. The count threshold applies to both performance branches because of the parentheses.
WHERE and HAVING Together
SELECT region,
COUNT(*) AS completed_orders,
SUM(total_amount) AS revenue
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;
WHERE establishes a completed-order population in one period. HAVING applies activity and revenue thresholds to each regional group. This is the standard pattern when row and aggregate conditions both matter.
Filtering Groups After All Categories Are Included
Sometimes all rows must contribute to displayed metrics, while a specific category controls group eligibility. If a customer summary must count every order but retain only customers having a completed order, a WHERE status filter would remove pending and cancelled orders from the count.
SELECT customer_id,
COUNT(*) AS all_orders
FROM orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) > 0;
Conditional aggregation tests the required category without changing the complete group population.
Groups Having No Particular Category
Changing the conditional sum to zero finds customers with no completed order among their existing rows:
HAVING SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) = 0
This does not find customers with no orders at all because such customers have no group in the orders table. Start from customers with a LEFT JOIN when zero-order customers must appear.
Groups Having Both Conditions
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;
WHERE cannot solve this by keeping one status because each customer group needs evidence of both states. An alternative counts distinct required statuses, but separate expressions are often clearer.
Filtering with COUNT(DISTINCT)
SELECT customer_id,
COUNT(DISTINCT product_id) AS unique_products
FROM order_items
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) >= 10;
This counts unique non-NULL products, not item rows or units. Distinct is appropriate only when uniqueness matches the metric. It should not be added blindly to conceal duplication caused by a bad join.
Duplicate Detection
Aggregate filtering identifies duplicate keys by retaining groups with more than one row.
SELECT email,
COUNT(*) AS occurrences
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
Composite duplicates require every business-key column in GROUP BY. Normalization rules for case, spaces, punctuation, and locale must match the business definition. The grouped result locates duplicate keys; a later query should retrieve complete records before any cleanup.
Ratios and Percentages
Percentage filtering requires the correct numerator, denominator, numeric type, and sample-size policy.
SELECT test_suite,
COUNT(*) AS execution_count,
100.0 * SUM(CASE WHEN result = 'Failed' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS failure_percentage
FROM test_results
GROUP BY test_suite
HAVING COUNT(*) >= 20
AND 100.0 * SUM(CASE WHEN result = 'Failed' THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) > 10;
The decimal literal avoids integer division, NULLIF protects the denominator, and the minimum count avoids a high percentage based on too few executions. Decide whether skipped or aborted tests belong in the denominator.
Weighted Average Filtering
Averages of averages are often wrong because groups have different sizes. To filter products by average price across transactions, calculate a weighted numerator and denominator when each row represents multiple units.
SELECT product_id,
SUM(unit_price * quantity) / NULLIF(SUM(quantity), 0)
AS weighted_average_price
FROM order_items
GROUP BY product_id
HAVING SUM(unit_price * quantity) / NULLIF(SUM(quantity), 0) > 100;
Average monthly averages, average store rates, and average class scores have the same risk. Preserve counts or weights whenever summaries will be summarized again.
Filtering by Aggregate Range
MIN and MAX together can test spread, duration, or consistency. This query finds sensors whose observed range exceeds 20 units:
SELECT sensor_id,
MIN(reading) AS minimum_reading,
MAX(reading) AS maximum_reading
FROM sensor_readings
WHERE observed_at >= :start_time
AND observed_at < :end_time
GROUP BY sensor_id
HAVING MAX(reading) - MIN(reading) > 20;
A wide range may indicate legitimate variation, a unit mismatch, or a bad sensor. Aggregate filtering identifies candidates; domain investigation determines cause.
Aggregate Filtering and NULL
COUNT star counts rows, COUNT expression counts non-NULL values, and other common aggregates ignore NULL inputs. An all-NULL measure group has a count of zero for that expression and usually NULL for SUM, AVG, MIN, and MAX. A comparison with NULL is unknown, so the group does not pass an ordinary threshold.
COALESCE changes meaning. COALESCE(SUM(amount), 0) is correct only when no known values should be treated as a zero total. Unknown and zero are not automatically equivalent.
Filtering Groups by Missing Values
-- At least one missing email
HAVING COUNT(email) < COUNT(*)
-- No missing emails
HAVING COUNT(email) = COUNT(*)
-- Every email is missing
HAVING COUNT(email) = 0
These conditions operate within existing groups. A group with no source rows does not exist unless an outer join or dimension table creates it.
Zero Is Different from NULL
A sum of zero may mean positive and negative values cancelled, all stored values were zero, or a business calculation produced zero. A NULL sum may mean every input was NULL or no matching measure existed after an outer join. Tests and labels should preserve these differences.
Date-Based Aggregate Filtering
MIN and MAX dates support first-activity, last-activity, and activity-span conditions.
SELECT customer_id,
MIN(order_date) AS first_order,
MAX(order_date) AS last_order
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
HAVING MAX(order_date) < :inactive_before;
This keeps customers whose latest completed order is older than the cutoff. Customers with no completed order require an outer join and explicit NULL handling.
Date Filter vs Date Aggregate Filter
WHERE order_date >= :start restricts which orders participate. HAVING MAX(order_date) >= :start retains customers whose group contains a sufficiently recent order while leaving older orders in other displayed aggregates. The two forms answer different questions.
Correct Date Grain and Time Zone
Filtering daily or monthly aggregates requires a properly derived calendar key. Grouping only by month number combines different years. Deriving a date in UTC can misclassify events when business days use another timezone. Convert to the reporting zone before grouping and use half-open timestamp ranges to avoid boundary overlap.
Aggregate Filtering with JOIN
A customer-to-order join can count orders by customer, but joining order items or payments changes the input grain. HAVING evaluates aggregates after all joins, so duplicated rows can make groups pass thresholds incorrectly.
SELECT c.customer_id,
COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING COUNT(o.order_id) >= 5;
This is correct if one joined row represents one order. If another one-to-many table is added, count a distinct order key only when that is genuinely the intended measure, or pre-aggregate.
Finding Entities with No Matches
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 star would count the preserved outer row and return one, so the non-NULL child key is necessary. NOT EXISTS can express the same absence requirement more directly and may avoid grouping entirely.
JOIN Multiplication Cannot Be Repaired by HAVING
If an order has three items and two payments, joining all three tables creates six combinations. SUM and COUNT then process those six rows. HAVING only tests the corrupted results; it cannot infer which duplication was unintended.
Validate relationship cardinality and aggregate each child table to one row per parent before combining measures with different grains.
Pre-Aggregating Child Tables
WITH item_totals AS (
SELECT order_id,
SUM(quantity * unit_price) AS item_total
FROM order_items
GROUP BY order_id
),
customer_totals AS (
SELECT o.customer_id,
COUNT(*) AS order_count,
SUM(i.item_total) AS total_spend
FROM orders o
JOIN item_totals i ON i.order_id = o.order_id
GROUP BY o.customer_id
)
SELECT *
FROM customer_totals
WHERE total_spend > 10000;
Each stage declares its grain: one row per order, then one row per customer. The final outer filter is easy to read and test.
HAVING vs Filtering a Derived Table
HAVING is concise when conditions directly relate to one grouping level. A CTE or derived table is useful when aggregate expressions are long, aliases are needed, several later operations reuse the summary, or multiple aggregation stages are required.
WITH department_summary AS (
SELECT department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
)
SELECT *
FROM department_summary
WHERE employee_count >= 10
AND average_salary > 70000;
The outer WHERE filters summary rows, not source employees. This design is not a workaround; it is a clear expression of staged query grain.
Comparing Groups with an Overall Aggregate
SELECT department,
AVG(salary) AS department_average
FROM employees
GROUP BY department
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);
Both the department average and company average must use compatible populations. If active employees are filtered outside but not inside the subquery, the comparison changes. Repeat or centralize shared filters deliberately.
Two Aggregation Levels
Questions such as “which months exceeded average monthly sales?” require monthly aggregation first and an average of those monthly totals second.
WITH monthly_sales AS (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
SUM(total_amount) AS monthly_total
FROM orders
WHERE status = 'Completed'
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
),
benchmark AS (
SELECT AVG(monthly_total) AS average_monthly_total
FROM monthly_sales
)
SELECT m.*
FROM monthly_sales m
CROSS JOIN benchmark b
WHERE m.monthly_total > b.average_monthly_total;
The first stage creates one row per month. The second summarizes months, not orders. Mixing these levels in one unstructured expression often produces invalid or misleading SQL.
Aggregate Filtering vs Window Filtering
GROUP BY and HAVING collapse rows. If the result must retain each employee while keeping only employees from high-average departments, calculate a window metric and filter it in an outer query.
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;
The desired output grain chooses the technique: one row per department suggests GROUP BY; one row per employee with department context suggests a window.
Aggregate Filtering vs EXISTS
If the requirement is only that at least one related row exists, EXISTS can communicate intent more directly than grouping and counting.
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'Completed'
);
Use grouping when the count, sum, average, or threshold itself matters. Use EXISTS or NOT EXISTS when the question is purely presence or absence.
Snapshot Metrics Are Not Always Additive
Inventory balance, account balance, active subscriber count, and storage usage are often snapshots. Summing daily balances usually does not produce a meaningful monthly balance. Aggregate filtering must use the correct metric, perhaps latest balance, average daily balance, or maximum capacity, rather than defaulting to SUM.
Define whether a metric is additive across time, entities, and dimensions before filtering it. Semi-additive measures are a common source of plausible but incorrect reports.
Mixing Units and Currencies
A sum or average across incompatible units has no coherent meaning. Convert currencies using an approved rate and date, normalize durations to one unit, and preserve measurement context before aggregation. Filter thresholds must use the same unit as the calculated metric.
Precision and Threshold Boundaries
Define whether thresholds apply to exact aggregates or displayed rounded values. A precise average of 69.995 may display as 70.00 but fail a comparison with 70, depending on type and rule. Use exact decimal types for money and test groups immediately below, equal to, and above each boundary.
Define a Metric Contract Before Filtering
A metric contract documents the population, grain, expression, unit, NULL rule, time basis, and threshold. For “high-value customer,” the contract might specify completed orders only, one group per customer, net amount after refunds in USD, a rolling 365-day period in UTC, and a strict total greater than 10,000. Without those details, two correct-looking queries can classify different customers.
Keep metric definitions consistent across dashboards, exports, alerts, and application code. A central semantic layer, reviewed SQL view, or versioned specification can prevent teams from independently interpreting the same business phrase.
Threshold Versioning and Effective Dates
Business thresholds change. A fraud-review amount, service-level target, or minimum order count used today may differ from last year. Hard-coding the current value into a historical report can reclassify old periods under a rule that did not exist then.
When historical classification matters, store threshold versions with effective dates and join each reporting period to the applicable rule. If the report intentionally restates history under today's rule, label that behavior. Parameterized thresholds should also record the value used in exported reports so results can be reproduced.
Explain Why a Group Passed
A report is easier to trust when it displays the metrics used by its filter. If an endpoint passes because either latency or error percentage is excessive, include both values and optionally a derived reason.
SELECT endpoint,
AVG(response_time_ms) AS average_ms,
100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS error_percentage,
CASE
WHEN AVG(response_time_ms) > 500 THEN 'Latency'
ELSE 'Errors'
END AS primary_reason
FROM api_requests
GROUP BY endpoint
HAVING AVG(response_time_ms) > 500
OR 100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) > 2;
If both conditions can pass, a more detailed CASE should report both or prioritize them explicitly. Explainability is especially important when filtered results trigger alerts, reviews, or customer-impacting actions.
Audit the Rejected Groups
Testing only the groups that pass can hide boundary and population defects. During validation, materialize the summary before filtering and inspect passing and rejected groups together. Add a status column such as 'Pass' or 'Reject' based on the same threshold expression.
This approach reveals groups that are unexpectedly close to the boundary, groups with NULL metrics, and categories omitted before aggregation. Once validated, the production query can apply the final filter, while monitoring can track changes in pass rates over time.
Separate Eligibility from Ranking
Aggregate filtering and ranking solve different tasks. HAVING can define eligible products with at least 100 reviews and an average rating of 4.5. ORDER BY and a row limit can then select the top ten among those products. Applying a top-ten limit before eligibility would change the candidate population.
For ties, use ranking functions over the prepared summary and document whether all tied groups should be included. The workflow is: aggregate, filter eligibility, rank qualifying summaries, and present the requested result.
Performance Considerations
Aggregate filtering may require scans, joins, sorting or hashing, memory, temporary storage, and data movement before groups can be rejected. A selective HAVING condition does not eliminate the cost of calculating candidate groups.
Apply correct row filters early, use sargable timestamp ranges instead of wrapping filtered columns in functions, avoid unnecessary group dimensions, and inspect join cardinality. Indexes on selective filters and join keys may reduce input work, while engine-specific indexes or ordered access can assist grouping. Verify with execution plans and representative volume.
High-cardinality keys create many groups; skewed keys create a few very large groups. In distributed systems, both can cause expensive shuffles or uneven work. Frequently requested summaries may benefit from materialized views, but refresh and late-data behavior must be explicit.
Transaction and Snapshot Consistency
Concurrent changes can move a group across a threshold while a report runs. Related dashboard queries may describe different moments under some isolation levels. Use a consistent snapshot, an explicit cutoff, or a prepared reporting table when totals must reconcile, and display the as-of time.
Security and Privacy
Tenant and authorization filters must be applied before aggregation so unauthorized rows never influence counts or totals. HAVING minimum group sizes can reduce exposure of small sensitive groups, but overlapping queries may still permit inference. Regulated reports need reviewed access and suppression policies.
Testing Aggregate Filters
Tests should include empty input, one group, multiple groups, threshold boundaries, NULL group keys, all-NULL measures, zero and negative values, duplicate values, and tiny samples. Join tests should include one-to-many and many-to-many data that reveals multiplication.
For ratios, test integer division, zero denominators, and rounding. For dates, test period boundaries, leap days, timezones, and daylight-saving changes. For staged aggregation, verify every intermediate grain independently before trusting the final filter.
Common Mistakes
- Placing an aggregate directly in WHERE at the same query level.
- Using HAVING for every condition instead of filtering eligible rows early.
- Filtering too early when all categories were needed in displayed metrics.
- Counting joined rows instead of the intended business entity.
- Using DISTINCT to hide incorrect join multiplication.
- Using
SUM(DISTINCT amount)and losing legitimate equal amounts. - Ignoring NULL and treating unknown as zero.
- Filtering an average without a minimum sample size.
- Averaging averages without weights.
- Grouping dates at the wrong calendar level or timezone.
- Mixing currencies, durations, or measurement units.
- Using GROUP BY when EXISTS better expresses presence.
Aggregate Filtering Checklist
- Define what one source row represents.
- Define what one output group represents.
- Identify rows that should participate before aggregation.
- Choose WHERE, HAVING, outer WHERE, EXISTS, or a window deliberately.
- Confirm COUNT is counting the intended entity.
- Document NULL, zero, negative, and missing-group behavior.
- Validate join cardinality and pre-aggregate when needed.
- Use correct denominators, weights, units, and currencies.
- Apply a minimum sample size where conclusions need stability.
- Use the correct date grain, timezone, and period boundaries.
- Test threshold equality and rounding.
- Inspect execution plans and snapshot consistency.
Interview Questions and Answers
What is aggregate filtering?
It is filtering based on summary values produced by aggregate functions, commonly using GROUP BY and HAVING or an outer query over a prepared summary.
Which clause directly filters grouped aggregate results?
HAVING. WHERE filters source rows before grouping.
Why can aggregate filtering be wrong after a join?
The join may multiply business entities before aggregation. HAVING then tests corrupted counts or totals. Validate cardinality and pre-aggregate child tables.
When is conditional aggregation required?
When all rows must remain in the group metrics but group eligibility depends on particular categories or conditions.
When should a window function be used?
When group metrics are needed for filtering but individual detail rows must remain in the result.
Why should average filters include sample size?
A high or low average based on very few observations may be unstable and misleading.
What is the danger of averaging averages?
Groups with different observation counts receive equal weight. Preserve numerators and denominators or calculate a weighted average.
Complete Real-World Example
SELECT endpoint,
COUNT(*) AS request_count,
COUNT(DISTINCT client_id) AS client_count,
AVG(response_time_ms) AS average_response_ms,
MAX(response_time_ms) AS maximum_response_ms,
100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) AS server_error_percentage
FROM api_requests
WHERE observed_at >= :period_start
AND observed_at < :period_end
AND environment = 'Production'
GROUP BY endpoint
HAVING COUNT(*) >= 100
AND (AVG(response_time_ms) > 500
OR 100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
/ NULLIF(COUNT(*), 0) > 2)
ORDER BY average_response_ms DESC, endpoint;
The source population is production requests in one period. Each output row represents one endpoint. The filter excludes low-volume endpoints and retains those with slow average latency or excessive server errors. The final order presents the slowest qualifying endpoints first.
Complete Mental Model
Source grain
+ row eligibility
+ safe joins
= aggregate input
Aggregate input
+ group key
+ metric definition
= group summaries
Group summaries
+ thresholds
+ NULL and boundary rules
= filtered aggregate result
Every stage has its own grain and rules. Make them visible through names, CTEs, tests, and report definitions.
Key Takeaway
Aggregate filtering is the process of selecting summaries rather than individual source rows. HAVING is central, but correct design begins earlier with source grain, row filters, joins, metric meaning, and NULL behavior, and may continue into nested queries or windows.
RELIABLE AGGREGATE FILTER = CORRECT INPUT ROWS
+ EXPLICIT GROUP GRAIN
+ VALID METRIC
+ SAFE CARDINALITY
+ CORRECT THRESHOLD
+ DEFINED NULL RULES
+ VERIFIED BOUNDARIES
Before writing the condition, state what is being measured, over which rows, at what grain, and why a group should pass. That statement determines the correct SQL pattern.