PostgreSQL FILTER aggregates apply a condition to the rows entering one aggregate while allowing other aggregates in the same query to use different conditions. They can make a report clearer than several loosely related subqueries. The convenience still needs an explicit definition of each measure and its population.
A reliable report distinguishes global row filtering, individual aggregate filtering, and filtering of completed groups. Those stages answer different questions, and mixing them can produce internally plausible but misleading totals.
Define every measure before writing SQL
List the measures and their populations. A report might need all requests, successful requests, failed requests, and a ratio based only on completed requests.
Do not assume those categories sum to the total. Pending, canceled, or unknown states may legitimately sit outside success and failure, and one record can satisfy several overlapping conditions.
Write the definitions in business terms first. The SQL should implement those definitions rather than introducing accidental meaning through whichever predicate was easiest to express.
Keep shared scope in WHERE
The query’s WHERE clause restricts the common input set before aggregation. Use it for the time range, tenant, and other scope all measures should share.
An individual FILTER changes only the input to its aggregate. It does not automatically remove those rows from every other measure in the query.
That distinction is useful when computing related metrics from one shared source. It also means access restrictions should not appear only in one aggregate filter while another aggregate still counts unauthorized rows.
Use a readable per-measure example
SELECT
count(*) AS total_requests,
count(*) FILTER (WHERE status = 'success') AS successful,
count(*) FILTER (WHERE status = 'failure') AS failed
FROM request_log
WHERE tenant_id = $1;The example assumes a reviewed schema and parameter binding through the client. It intentionally leaves other states visible in the total.
Do not label total minus successful as failures unless that relationship is part of the data contract. Unknown and pending records can otherwise be misclassified.
Understand true, false, and null predicates
Only rows whose filter condition evaluates to true enter that aggregate. A null result is not true and is therefore excluded.
If status can be null, comparisons to success or failure do not classify those records automatically. Decide whether missing status is a separate category, an error, or an allowed unknown state.
Test null explicitly. A report that appears balanced on complete test data can silently undercount a category when production records include incomplete fields.
Choose count star or count expression deliberately
Count star counts qualifying rows. Counting a particular expression excludes null values of that expression, even after the FILTER condition is satisfied.
For example, counting completed rows is different from counting completed rows with a known external identifier. Both can be useful, but they need different names.
Review every aggregate’s null semantics. A filtered sum with no qualifying values can also require a deliberate display policy rather than blindly replacing every null result with zero.
Combine DISTINCT without losing meaning
A filtered distinct count counts distinct qualifying expression values under the aggregate’s semantics. It does not count every event or every person in the common source population.
Define whether one entity can appear in several categories and whether category totals are meant to be additive. Summing distinct counts across groups can double-count an entity shared between them.
Validate using a fixture where the same user has both successful and failed requests. That exposes a mistaken assumption that per-category distinct users partition the total user population.
Keep denominators explicit
Ratios need a clearly defined denominator and a policy for zero or absent denominator values. A success rate among completed requests differs from a rate among all accepted requests.
Use the appropriate numeric arithmetic so integer division or rounding does not silently alter the result. Choose precision and display rounding according to the report’s purpose.
Do not compare ratios from queries with different global filters as though they describe the same population. A compact query is useful precisely because it can keep related measures aligned under one documented scope.
Check joins before trusting aggregate totals
A one-to-many join can multiply each source record before aggregation. Several filtered measures can then inflate together and still look mutually consistent.
Review join cardinality and decide whether the measure concerns source records, joined rows, or distinct entities. Aggregate at the appropriate level before joining when the model requires it.
Test a source record with multiple related rows. The expected total should remain correct according to the measure definition rather than depending on the accidental size of its joined detail set.
Distinguish FILTER from HAVING
FILTER determines which input rows feed one aggregate. HAVING decides which completed groups remain in the output based on group-level conditions.
A HAVING clause can remove a group from the displayed report without changing how its aggregates were computed. Do not use that disappearance as evidence that the underlying records were outside the population.
Document whether the user sees all groups or only groups meeting a reporting threshold. Hidden groups can matter when later consumers try to reconcile displayed sums with a grand total.
Verify performance and empty outcomes
Multiple measures in one query can simplify work, but do not promise a particular execution cost without inspecting the actual plan and representative workload.
Test empty input, all-null values, no qualifying rows, overlapping categories, and a zero denominator. Confirm the API and UI distinguish unknown, zero, and unavailable states appropriately.
Preserve metric definitions with the query version. A changed predicate can alter historical comparability even when the SQL output columns retain the same familiar names.
Build a hand-checkable report fixture
Use a small set of records with success, failure, pending, null status, repeated users, and multiple joined details. Write the expected count and population of each measure independently.
Then run the actual application query and compare the outputs. This checks both SQL semantics and any client conversion or display behavior.
FILTER is most valuable when each measure’s scope is obvious. The resulting report should make its arithmetic explainable without asking readers to infer the meaning of every excluded row.
Frequently asked questions
Does FILTER affect all aggregates in the select list?
No. It applies to the aggregate to which it is attached.
Are null filter conditions accepted?
Only true conditions qualify. Explicitly define how missing source values should be reported.
Can category distinct counts always be added?
No. Shared entities and overlapping categories can cause double counting.
Consult the PostgreSQL aggregate-expression documentation for FILTER, null handling, and DISTINCT syntax.
For a complementary workflow, read PostgreSQL Grouping Sets: Subtotals With Clear Row Meaning.
