Materialize Documentation
Join the Community github/materialize

Aggregate function filters

You can use a FILTER clause on an aggregate function to specify which rows are sent to an aggregate function. Rows for which the filter_clause evaluates to false are discarded.


aggregate_name ( expression ) FILTER ( WHERE filter_clause )


    COUNT(*) AS unfiltered,
    COUNT(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i)