SQLite aggregate filters keep related measurements in one query

A report often needs several measurements over the same population. Filtering the entire query down to one category makes that category easy to count, but removes rows needed by the other measurements. SQLite 3.30.0, released on 4 October, adds FILTER clauses on aggregate functions. Each aggregate can now state which input rows contribute while the query retains its broader population.

The same release adds NULLS FIRST and NULLS LAST to ordering clauses. These changes fit together in reporting queries. Conditional aggregates can produce missing results when no rows qualify, and the report then needs a deliberate position for those results. SQL can express both decisions without substituting invented numeric values for missing observations.

I like SQLite for analyses that belong with a local database file. A report does not need to become a separate application merely because its measurements use different predicates. Having that distinction in the aggregate expression makes the query easier to inspect, particularly when a reader needs to understand which population each number describes.

Keep the population separate from each measure

An illustrative service report can count every request while calculating an average over successful requests alone. A second filtered aggregate counts errors without changing the input available to the first two calculations.

SELECT service,
       count(*) AS requests,
       avg(duration_ms) FILTER (WHERE status = 'ok') AS success_mean_ms,
       count(*) FILTER (WHERE status = 'error') AS errors
FROM requests
GROUP BY service
ORDER BY success_mean_ms NULLS LAST, service;

The query's population is all rows in requests. Grouping organizes them by service. Each filtered aggregate then admits its own subset from that group. A service whose rows contain no successful requests still has a group, a request count and an error count. Its missing successful-request average does not require dropping the service from the report.

Moving status = 'ok' into a top-level WHERE would change that behavior. Only successful requests would reach grouping. The total count would then count successful requests, and the error aggregate would have no error rows available. A service with no successful rows would disappear altogether. The predicate's location determines the population on which subsequent calculations operate.

The release's aggregate filter tests exercise several filters in one result. With integer inputs, one sum accepts values above one boundary and another uses a different boundary. Other cases combine filters with grouping and ordering by the computed aggregate. The feature is therefore useful for related measurements within a single ordinary aggregate query, without requiring a window expression.

The filter controls the aggregate step

The 3.30.0 aggregate code generator shows where the condition applies. Before generating argument evaluation and the aggregate step, it emits a conditional jump for the filter expression. False and null filter results take the skip path. A row that does not satisfy that aggregate's condition does not contribute to its accumulator.

This placement gives the syntax a direct meaning. The condition belongs to the aggregate invocation. It does not become an implicit HAVING clause over a finished result, and it does not filter every other aggregate in the select list. Each function maintains the input rules expressed beside that function.

The implementation also places the filter check before its distinct-value handling. A filtered distinct aggregate consequently restricts the eligible inputs before admitting distinct argument values to the calculation. The order of these operations matters when the same value appears in both qualifying and nonqualifying rows. The filter determines which rows are eligible to supply that value.

The code contains special handling for filtered minimum and maximum calculations alongside ordinary selected columns. Its comment discusses preserving the accumulator's row values when a filter skips the minimum or maximum invocation. This is a reminder that aggregate execution involves more than evaluating a final arithmetic expression. SQLite has to coordinate aggregate state with the grouped query's other output registers. The release tests include a grouped maximum whose filter accepts no rows, so that this skipped-update case receives direct coverage as well.

The tests reject a filter on a scalar function such as upper. They also reject aggregate and window expressions misused inside the filter condition. This syntax is not a general way to add conditional execution to every SQL function call. It extends aggregate input selection, with the same need to respect the query's evaluation contexts as other aggregate expressions.

Conditional expressions have different traps

A conditional CASE expression can already feed selected values to an aggregate. For an average, returning null for the excluded cases expresses a different calculation from returning zero. The latter includes invented zero values in the denominator. A filter expresses exclusion directly, without requiring the programmer to encode it through a replacement argument value.

SELECT
  avg(CASE WHEN status = 'ok' THEN duration_ms END) AS conditional_mean,
  avg(duration_ms) FILTER (WHERE status = 'ok') AS filtered_mean
FROM requests;

The example deliberately omits an ELSE 0. Its purpose is to compare two expressions that exclude unsuccessful rows from this average. It does not establish a general textual replacement rule for every aggregate and conditional expression. The aggregate's treatment of null arguments and empty input remains part of its semantics.

The aggregate implementations in this release make that distinction concrete. sum returns null when no non-null inputs contribute, while total returns a floating-point zero. The average counts non-null contributing values and divides their sum by that count. A filter does not change these finalization rules. It changes which rows reach the aggregate.

Counting has a related distinction. count(*) counts eligible rows. count(duration_ms) counts eligible rows whose argument is non-null. A request can satisfy the filter but lack a recorded duration. In that case it contributes to the former count and not to the latter. A report describing coverage needs to distinguish a missing measurement from the absence of an eligible request.

Put missing results where the report needs them

The service query sorts by its computed successful-request average and places null results last. That leaves services with measured successful requests ordered by their actual values while retaining services without such a measurement. Assigning zero to the latter would imply a measured duration that the query never observed.

The NULL ordering regression tests cover ascending and descending ordering with both explicit null placements. Null position is independent of direction. Descending values can still put nulls first, and ascending values can put them last. This lets a report choose the presentation of missing data without reversing its numeric ranking.

Multiple ordering terms remain useful. The service name in the example orders rows that have the same average, including services whose averages are all null. Explicit null placement determines a category of ordering, not the entire order among every row in that category. A second term states the remaining presentation choice.

The tests also exercise indexed tables and corresponding query plans. Certain queries with explicit null placement still use an index, including cases that constrain a leading indexed column. The new syntax should therefore not be equated with an unconditional temporary sort. Index use depends on the whole query and index definition, as the differing regression cases demonstrate.

Ordering syntax is not an index declaration

The release supports the new words in ORDER BY, but the tests explicitly reject them in index definitions. They also reject null-placement syntax in primary-key and unique declarations and in conflict targets. That boundary prevents reading the feature as a new persistent storage-order setting. It is a way to request result ordering in a query.

A query author does not need to change the schema simply to put missing averages at the bottom of a report. Conversely, adding NULLS LAST to a query does not create an index that stores nulls differently. Those are separate requests, and this release implements the former. The source tests are useful here because the accepted grammar in one context does not imply acceptance everywhere an ordering term appears.

I favor keeping missing measurements visible. A null average can tell the reader that a service has requests but lacks successful measurements for this calculation. The combination of a filtered aggregate and explicit null ordering preserves that information while keeping the measured values comparable.

The resulting query can express its population, each measurement's eligibility rule and the position of missing results in one place. Services with no successful inputs remain present because filtering belongs to the average rather than to the whole query. Their position at the end follows from the ordering clause, while their missing value remains available to an API consumer as missing data.