FILTER keyword

FILTER (WHERE ...) restricts the rows that a single aggregate function sees, without affecting the other aggregates in the same query. It replaces the conditional sum(CASE WHEN ... THEN ... END) pattern with the SQL:2003 form used by PostgreSQL, DuckDB, SQLite and Trino, so queries written elsewhere and dashboards generated by BI tools work unchanged.

The condition sits next to the aggregate it belongs to, and each column in the select list can carry its own condition over a single scan of the table:

Conditional aggregation with FILTER
SELECT
    symbol,
    count(*)   FILTER (WHERE quantity >= 250_000) AS large_trades,
    avg(price) FILTER (WHERE side = 'buy')        AS avg_buy_price
FROM fx_trades;
The same query without FILTER
SELECT
    symbol,
    count(CASE WHEN quantity >= 250_000 THEN 1 END) AS large_trades,
    avg(CASE WHEN side = 'buy' THEN price END)      AS avg_buy_price
FROM fx_trades;

FILTER attaches to the aggregate itself, so it works everywhere an aggregate does: explicit and implicit GROUP BY, SAMPLE BY buckets, PIVOT aggregates, and aggregates used in window position with OVER. It does not apply to window functions that are not aggregates, such as row_number, lag or first_value.

Syntax​

aggregateFunction ( [ arguments ] )
    FILTER ( WHERE condition )
    [ OVER ( windowSpecification ) ]
  • The clause goes immediately after the aggregate's closing parenthesis. With a window function, FILTER comes first and OVER second.
  • condition is any boolean expression valid in a WHERE clause, including IN, BETWEEN, IS NULL, casts, CASE, bind variables, DECLARE variables and scalar sub-queries.
  • Arguments that configure the call rather than carry row data are left alone, so approx_percentile(x, 0.5) FILTER (...) still computes the median.
  • FILTER is not a reserved word. It only starts the clause when a ( follows it, so an existing column or alias named filter keeps working.

Examples​

Bucketing​

Each bucket is one column, and adding a bucket means adding one line:

Trade size distribution per symbolDemo this query
SELECT
    symbol,
    count(*) FILTER (WHERE quantity < 50_000)   AS small,
    count(*) FILTER (WHERE quantity >= 50_000
                       AND quantity < 250_000)  AS medium,
    count(*) FILTER (WHERE quantity >= 250_000) AS large
FROM fx_trades
WHERE timestamp IN '$today'
ORDER BY symbol;

SAMPLE BY​

Buy and sell counts per minuteDemo this query
SELECT
    timestamp,
    count(*) FILTER (WHERE side = 'buy')  AS buys,
    count(*) FILTER (WHERE side = 'sell') AS sells
FROM fx_trades
WHERE symbol = 'EURUSD' AND timestamp IN '$now - 1h..$now'
SAMPLE BY 1m;

ASOF JOIN​

The condition can reference both sides of a join:

Trades outside the prevailing quoteDemo this query
SELECT
    t.symbol,
    count(*) FILTER (WHERE t.price > c.ask_price) AS above_ask,
    count(*) FILTER (WHERE t.price < c.bid_price) AS below_bid,
    count(*)                                      AS total
FROM fx_trades t
ASOF JOIN core_price c ON (symbol)
WHERE t.timestamp IN '$now - 15m..$now'
ORDER BY t.symbol;

Window functions​

An aggregate used with OVER accepts FILTER too. Here the running total advances only on buy trades, while every row of the result is kept:

Running buy volume per symbolDemo this query
SELECT
    timestamp,
    symbol,
    side,
    quantity,
    sum(quantity) FILTER (WHERE side = 'buy')
        OVER (PARTITION BY symbol ORDER BY timestamp) AS cumulative_buy
FROM fx_trades
WHERE timestamp IN '$now - 1m..$now';

cumulative_buy is null until the first buy trade appears in each symbol's partition, since until then the frame holds no matching row.

Naming a condition with DECLARE​

SQL has no named-filter construct, but a DECLARE variable can hold an entire boolean expression, so several aggregates can share one condition instead of repeating it:

Large-fill share per symbolDemo this query
DECLARE @large := (quantity >= 250_000)
SELECT
    symbol,
    count(*)      AS all_fills,
    sum(quantity) AS all_volume,
    count(*)      FILTER (WHERE @large) AS large_fills,
    sum(quantity) FILTER (WHERE @large) AS large_volume,
    sum(quantity) FILTER (WHERE @large) / sum(quantity) AS large_share
FROM fx_trades
WHERE timestamp IN '$today'
ORDER BY symbol;
symbolall_fillsall_volumelarge_fillslarge_volumelarge_share
AUDCAD2270291882172175632980820.2169
AUDJPY2402316135548204746287100.2361

The unfiltered totals are what make FILTER necessary here. If every aggregate shared the condition, the right move is to drop FILTER and put quantity >= 250_000 in the WHERE clause instead.

The variable is substituted into each condition before the query is planned, so this is identical to writing quantity >= 250_000 out three times. Multi-clause conditions work the same way, and the parentheses are optional:

DECLARE @big_buy := quantity >= 250_000 AND side = 'buy'

The same variable works in window position, where repeating a long condition across several OVER clauses costs the most.

FILTER or PIVOT​

These two produce the same result:

Buy and sell activity with FILTERDemo this query
SELECT
    symbol,
    count(*)      FILTER (WHERE side = 'buy')  AS buy_trades,
    sum(quantity) FILTER (WHERE side = 'buy')  AS buy_volume,
    count(*)      FILTER (WHERE side = 'sell') AS sell_trades,
    sum(quantity) FILTER (WHERE side = 'sell') AS sell_volume
FROM fx_trades
WHERE timestamp IN '$today'
ORDER BY symbol;
The same result with PIVOTDemo this query
SELECT * FROM fx_trades
WHERE timestamp IN '$today'
PIVOT (
    count(*) AS trades, sum(quantity) AS volume
    FOR side IN ('buy', 'sell')
    GROUP BY symbol
)
ORDER BY symbol;

PIVOT is the better fit here: every output column applies the same aggregates to the distinct values of one column, and it names the columns for you. Reach for FILTER when the conditions are not simply the distinct values of one column, such as the overlapping ranges in the bucketing example above, conditions on different columns, or conditions spanning both sides of a join.

Empty results​

When nothing matches, count returns 0 and every other aggregate returns NULL, the same as it would over an empty table:

What each form returns when nothing matchesDemo this query
SELECT
    sum(CASE WHEN price > 10_000 THEN quantity END)          AS case_form,
    sum(CASE WHEN price > 10_000 THEN quantity ELSE 0 END)   AS case_zeroed,
    sum(quantity) FILTER (WHERE price > 10_000)              AS filter_form,
    coalesce(sum(quantity) FILTER (WHERE price > 10_000), 0) AS filter_zeroed,
    count(*) FILTER (WHERE price > 10_000)                   AS count_form
FROM fx_trades
WHERE timestamp IN '$today';
case_formcase_zeroedfilter_formfilter_zeroedcount_form
null0.0null0.00

case_form and filter_form agree. The NULL is ordinary SQL, not something FILTER introduces: a plain sum(quantity) whose WHERE clause matches nothing returns NULL too.

The zero came from ELSE 0, which contributed a value for every non-matching row. FILTER has no ELSE, so when something downstream cannot take a null, a Grafana panel or a non-nullable target column, wrap the aggregate in coalesce(). count never needs it.

Groups are never dropped. A symbol where nothing matched still appears, so a histogram never loses a bucket:

Non-matching groups are keptDemo this query
SELECT
    symbol,
    count(*)      FILTER (WHERE price > 10_000) AS matched,
    sum(quantity) FILTER (WHERE price > 10_000) AS matched_volume
FROM fx_trades
WHERE timestamp IN '$today'
ORDER BY symbol
LIMIT 2;
symbolmatchedmatched_volume
AUDCAD0null
AUDJPY0null

FILTER narrows which rows the aggregate sees. It does not change how the aggregate treats NULL in the rows that remain, so count(x) FILTER (WHERE c) counts rows where c holds and x is not null.

Limitations​

Aggregates that do not accept FILTER​

AggregateUse instead
first, lastfirst_not_null and last_not_null, which accept FILTER
array_agg, bool_and, bool_or, mode, isOrdered, twapFiltering the rows first, see below
FILTER is not supported for 'first', filter rows in a subquery instead

These are rejected because a non-matching row would still reach the aggregate as a real value rather than a NULL, changing the answer. Narrow the rows in a CTE or sub-query instead:

Filtering in a CTEDemo this query
WITH buys AS (
    SELECT * FROM fx_trades
    WHERE side = 'buy' AND timestamp IN '$today'
)
SELECT symbol, first(price)
FROM buys;

Argument types that do not accept FILTER​

An argument resolving to BYTE, SHORT, CHAR or BOOLEAN is rejected. Those types have no distinct NULL, so an excluded row would reach the aggregate as a genuine 0 or false and be counted, averaged or compared like any other.

fx_trades.passive is a BOOLEAN, so counting it directly is rejected:

Rejected - BOOLEAN argument
SELECT count(passive) FILTER (WHERE side = 'buy') FROM fx_trades;
-- FILTER is not supported for a BOOLEAN argument, whose NULL is
-- indistinguishable from its zero value; cast the argument, for example
-- sum(x::int) FILTER (WHERE ...)

Cast the argument to a type that has a distinct NULL:

Passive buy trades, casting the argumentDemo this query
SELECT sum(CAST(passive AS INT)) FILTER (WHERE side = 'buy') AS passive_buys
FROM fx_trades
WHERE timestamp IN '$today';

This applies by resolved type, not by function name, so sum over a SHORT is rejected even though zero would happen to be harmless for it.

Conditions​

The condition is evaluated per row, so it may not contain an aggregate, a window function, or another FILTER:

Rejected conditions
SELECT sum(quantity) FILTER (WHERE sum(quantity) > 1) FROM fx_trades;
-- aggregate functions are not allowed in FILTER

SELECT sum(quantity) FILTER (WHERE row_number() OVER () > 1) FROM fx_trades;
-- window functions are not allowed in FILTER

SELECT sum(quantity) FILTER (WHERE abs(price) FILTER (WHERE false) > 0)
FROM fx_trades;
-- FILTER is not allowed inside a FILTER condition

A non-deterministic condition such as rnd_boolean() is rejected on aggregates taking more than one value argument, because each argument would draw its own verdict. Single-argument aggregates are unaffected, so sum(quantity) FILTER (WHERE rnd_double() < 0.1) is a valid sampling idiom.

Placement​

FILTER only attaches to an aggregate in the select list. Anywhere else it is rejected with FILTER is supported only for aggregate functions: on a scalar function such as abs(), on a window function that is not an aggregate, in a WHERE clause, in a join ON condition, and in SAMPLE BY's FROM, TO, FILL, timezone and offset expressions.

Inside a window specification the message differs:

Rejected - inside OVER
SELECT sum(quantity) OVER (PARTITION BY abs(price) FILTER (WHERE false))
FROM fx_trades;
-- FILTER is not supported in a window specification

Performance​

FILTER changes how a query is written, not how it runs. It is neither faster nor slower than the equivalent CASE expression, because it is compiled into one. EXPLAIN therefore reports the rewritten shape rather than what was typed:

EXPLAIN SELECT avg(price) FILTER (WHERE quantity > 1000) FROM fx_trades;

Async Group By workers: 8
  vectorized: false
  values: [avg(case([1000<quantity,price,null]))]
  filter: null
    PageFrame
        Row forward scan
        Frame forward scan on: fx_trades

Filtered aggregates run on the parallel Async Group By path, but not on the SIMD path, which needs a plain column argument and a single key column. The sum(CASE ...) form does not reach it either, so this is not a regression. The condition is evaluated once per filtered argument per row, so twice per row for a two-argument aggregate such as corr.

See also​