FILTER Clause in Aggregation
intermediateaggregation
2 min read
What is the FILTER Clause?
The FILTER clause allows you to apply a condition to a specific aggregate function without affecting other aggregates in the same query. It is a PostgreSQL extension that is cleaner than using CASE WHEN inside aggregates.
Syntax
SELECT
aggregate_function(column) FILTER (WHERE condition)
FROM table_name;When to Use
- Computing multiple conditional aggregates in a single query
- Counting rows that meet different criteria in one pass
- Replacing verbose CASE WHEN expressions inside aggregates
Key Points
- Per-Aggregate Filter — Each aggregate can have its own FILTER condition.
- Replaces CASE —
COUNT(*) FILTER (WHERE x)is equivalent toCOUNT(CASE WHEN x THEN 1 END)but more readable. - Any Aggregate — Works with COUNT, SUM, AVG, MIN, MAX, and others.
- PostgreSQL Specific — FILTER is not available in all databases.
- Performance — Avoids the overhead of CASE expression evaluation in some cases.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
FILTER Clause in Aggregation Challenge
Write a query that solve this task: count grade-A and grade-B students in one query.
Expected result
Three columns: count of A students, count of B students, and total count.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is the FILTER Clause?
Initializing database...Each run starts from fresh sample data.
More Examples
Sum with FILTER
Sum order totals for different product categories.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is FILTER standard SQL?
FILTER was added to the SQL:2003 standard but many databases have not implemented it. It works in PostgreSQL 9.4+.
Can I use FILTER with window functions?
Yes. In PostgreSQL you can use FILTER with windowed aggregates: SUM(x) FILTER (WHERE y) OVER (...).