sql / expert
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
1
2
3
4
5
6
SELECTregion,COUNT(*) FILTER (WHERE status = 'active') AS active_count,SUM(revenue) FILTER (WHERE date > '2025-01-01') AS ytd_revenueFROM sales_dataGROUP BY region;
Breakdown
1
COUNT(*) FILTER (WHERE status = 'active')
Aggregates only the rows that satisfy the specified predicate within the parentheses.
2
GROUP BY region
Partitions the result set for localized conditional analysis.