# FILTER keyword

FILTER (WHERE ...) restricts the rows an aggregate function sees in GROUP BY, SAMPLE BY and window queries, replacing conditional SUM(CASE ...) expressions with standard SQL.

`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:

```questdb-sql title="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;
```

```questdb-sql title="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`](/docs/query/sql/group-by/),
[`SAMPLE BY`](/docs/query/sql/sample-by/) buckets,
[`PIVOT`](/docs/query/sql/pivot/) aggregates, and aggregates used in window
position with [`OVER`](/docs/query/functions/window-functions/overview/). It does
not apply to window functions that are not aggregates, such as `row_number`,
`lag` or `first_value`.

## Syntax

```questdb-sql
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`](/docs/query/sql/where/) clause, including `IN`, `BETWEEN`,
  `IS NULL`, [casts](/docs/query/sql/cast/), [`CASE`](/docs/query/sql/case/),
  bind variables,
  [`DECLARE`](/docs/query/sql/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:

```questdb-sql demo title="Trade size distribution per symbol"
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

```questdb-sql demo title="Buy and sell counts per minute"
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:

```questdb-sql demo title="Trades outside the prevailing quote"
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:

```questdb-sql demo title="Running buy volume per symbol"
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`](/docs/query/sql/declare/)
variable can hold an entire boolean expression, so several aggregates can share
one condition instead of repeating it:

```questdb-sql demo title="Large-fill share per symbol"
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;
```

| symbol | all_fills | all_volume | large_fills | large_volume | large_share |
| ------ | --------- | ---------- | ----------- | ------------ | ----------- |
| AUDCAD | 2270      | 291882172  | 175         | 63298082     | 0.2169      |
| AUDJPY | 2402      | 316135548  | 204         | 74628710     | 0.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:

```questdb-sql
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:

```questdb-sql demo title="Buy and sell activity with FILTER"
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;
```

```questdb-sql demo title="The same result with PIVOT"
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`](/docs/query/sql/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:

```questdb-sql demo title="What each form returns when nothing matches"
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_form | case_zeroed | filter_form | filter_zeroed | count_form |
| --------- | ----------- | ----------- | ------------- | ---------- |
| null      | 0.0         | null        | 0.0           | 0          |

`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()`](/docs/query/functions/conditional/#coalesce). `count` never needs
it.

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

```questdb-sql demo title="Non-matching groups are kept"
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;
```

| symbol | matched | matched_volume |
| ------ | ------- | -------------- |
| AUDCAD | 0       | null           |
| AUDJPY | 0       | null           |

`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

| Aggregate | Use instead |
| --------- | ----------- |
| `first`, `last` | `first_not_null` and `last_not_null`, which accept `FILTER` |
| `array_agg`, `bool_and`, `bool_or`, `mode`, `isOrdered`, `twap` | Filtering 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](/docs/query/sql/with/) or sub-query instead:

```questdb-sql demo title="Filtering in a CTE"
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:

```questdb-sql title="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`:

```questdb-sql demo title="Passive buy trades, casting the argument"
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`:

```questdb-sql title="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:

```questdb-sql title="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`](/docs/query/sql/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

- [Aggregate functions](/docs/query/functions/aggregation/) - available
  aggregates
- [GROUP BY](/docs/query/sql/group-by/) - group rows for aggregation
- [PIVOT](/docs/query/sql/pivot/) - expand one column's values into columns
- [SAMPLE BY](/docs/query/sql/sample-by/) - time-series aggregation
- [Window functions](/docs/query/functions/window-functions/overview/) -
  calculations across related rows