SQL FILTER

FILTER is a clause defined in the SQL standard that lets you attach a Boolean predicate directly to an aggregate or window function. Instead of applying the condition in a separate WHERE or CASE expression, you write the predicate once inside FILTER (WHERE …). Only rows that satisfy the predicate are passed to the function; other rows in the result set are ignored for that specific calculation. This improves readability, prevents duplicated CASE logic, and can eliminate extra scans when the database optimizer can evaluate all aggregates in a single pass.FILTER works with any aggregate function (SUM, COUNT, AVG, array_agg, json_agg, percentile_cont, etc.) and with most window functions. It is evaluated after the FROM, WHERE, and GROUP BY clauses but before HAVING, making it logically equivalent to wrapping the input expression in CASE WHEN predicate THEN value END. Because the clause lives inside each function call, you can apply different predicates to multiple aggregates in the same SELECT without writing multiple CASE statements or subqueries.Key caveats:- Not all database engines implement FILTER. Where unsupported, you must fall back to CASE expressions.- The condition must appear inside parentheses immediately after the keyword WHERE.- FILTER cannot reference column aliases created in the same SELECT list.- For GROUP BY queries, FILTER applies within each group.- For window functions, FILTER applies within the window frame after PARTITION BY and ORDER BY have been processed.

• aggregate_function (text) - Any built-in or user defined aggregate or window function.
• expression (any) - Value fed into the aggregate (optional for COUNT(*)).
• condition (Boolean) - Row-level predicate that determines participation.

• Each aggregate returns a single scalar per group or per row (for window functions) that includes only rows meeting its FILTER predicate
• Other rows are disregarded for that function but remain visible to the query overall

• Producing multiple conditional aggregates in a single SELECT without repetitive CASE clauses
• Building KPI dashboards that compare totals across categories
• Calculating conditional window metrics such as active_days_per_user
• Replacing separate subqueries used solely to filter individual aggregates

• Using FILTER in a database that does not support it, leading to syntax errors
• Forgetting the WHERE keyword inside the parentheses
• Referencing column aliases inside the FILTER predicate
• Assuming FILTER replaces the outer WHERE clause; it only affects the specific function