Conditional Population Statistics via FILTER Clauses

Snippet

Conditional Population Statistics via FILTER Clauses

The FILTER clause is an ANSI-standard syntax for conditional aggregation. Unlike using CASE expressions inside aggregate functions, FILTER provides a clearer intent and potentially better optimization hints to the query planner by explicitly separating the selection predicate from the aggregation logic.

snippet.sql
sql
SELECT 
  region,
  COUNT(*) FILTER (WHERE status = 'active') AS active_count,
  SUM(revenue) FILTER (WHERE date > '2025-01-01') AS ytd_revenue
FROM sales_data
GROUP BY region;

Breakdown

COUNT(*) FILTER (WHERE status = 'active')

Aggregates only the rows that satisfy the specified predicate within the parentheses.

GROUP BY region

Partitions the result set for localized conditional analysis.

Previous snippet Next snippet

From your library

Conditional Population Statistics via FILTER Clauses

Related