FILTER Clause on Aggregates in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Conditional Aggregation (CASE inside Aggregates)
What does the FILTER clause do in SQL?
The FILTER clause attaches a condition directly to an aggregate function, restricting which rows that specific aggregate counts or sums, while leaving every other aggregate in the query unaffected.
The scenario: you're building a regional order dashboard. For each region, you need total orders, completed orders, and completed revenue — all in one row. You already know how to do this with CASE WHEN inside an aggregate. FILTER is a cleaner way to express the same thing. Instead of embedding a CASE expression inside the function, you place the condition after it:
SELECT
COUNT(*) AS total_orders,
COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_orders,
SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered_revenue
FROM ordersSELECT COUNT(*) AS total_orders, COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_count, COUNT(*) FILTER (WHERE status = 'pending') AS pending_count, SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered_revenue FROM orders
How does the FILTER clause work on an aggregate?
Each FILTER is independent. COUNT(*) FILTER (WHERE status = 'delivered') only counts delivered orders. The plain COUNT(*) still counts everything. A row excluded by one FILTER is still visible to all other aggregates in the same SELECT list.
The CASE WHEN equivalent for the completed count:
COUNT(CASE WHEN status = 'completed' THEN 1 END)Both produce the same number. FILTER is just easier to read — the condition sits right next to the function it constrains, instead of being buried inside a CASE expression wrapped around the argument.
FILTER works with every standard aggregate: COUNT, SUM, AVG, MIN, MAX, and others. When you have several conditional aggregates in one SELECT list, each targeting a different condition, FILTER makes the query much easier to scan:
SELECT
COUNT(*) FILTER (WHERE status = 'completed') AS completed,
COUNT(*) FILTER (WHERE status = 'pending') AS pending,
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM ordersThe same query with CASE WHEN would have three nested expressions to parse. With FILTER, each line states its condition plainly.
How does FILTER differ from CASE WHEN inside an aggregate?
The one thing that trips people up
FILTER excludes rows from the aggregate entirely. CASE WHEN excludes them by substituting NULL, which COUNT then ignores. For COUNT, the result is the same. For AVG, it can differ in subtle ways — FILTER removes the row from both the sum and the count, while CASE WHEN removes the value from the sum but not necessarily the count, depending on how it's written. For standard AVG(CASE WHEN ... END), behavior matches FILTER. Just be deliberate about which rows you want included in each calculation.
Should you use FILTER or CASE WHEN?
When to use FILTER vs CASE WHEN
For a SELECT list with several conditional aggregates targeting different conditions, FILTER is easier to scan — each function states its condition plainly on one line. For a single conditional aggregate embedded inside a complex expression, CASE WHEN often integrates more naturally. Both are correct; the choice is about which makes the query easier for someone else to read. On teams that use PostgreSQL, FILTER is increasingly the standard for multi-condition breakdowns.
Practice FILTER Clause in SQL
Brightlane's operations team is preparing an order status summary for the weekly review.
Write a query to return the total number of orders, the number of delivered orders, and the number of pending orders as a single row.
Assumptions:
- The
orderstable has one row per order with astatus. - The total count covers every order. The delivered count covers only orders with
status = 'delivered'. The pending count covers only orders withstatus = 'pending'.
Output:
- A single row with columns
total_orders,delivered_orders, andpending_orders.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 FILTER Clause practice problems
Write a query to return the total number of orders, the number of delivered orders, and the number of pending orders as a single row.
Write a query to return every customer ID, the total number of orders they have placed, and the number of those orders with status = 'delivered'.
Write a query to return every department ID, the total number of employees assigned to it, and the count of employees currently active.
Write a query to return every customer ID, their total order count, the number of delivered orders, and the total revenue from delivered orders only.
Write a query to return every category ID, the total number of products in that category, the count of products with price greater than $100, and the average price among products with price greater than $100.
Write a query to return every user ID, the user's total event count, the count of event_type = 'purchase' events, and the count of event_type = 'checkout' events.
Write a query to return every customer ID, their total order count, and the number of orders that are both delivered and have total_amount greater than $200.
Write a query to return every country, the total number of customers in that country, the count of customers with a missing city, and the count of customers with a recorded city.
Write a query to return every category ID, the total product count, the count of products priced above $1,000, and the combined price of those luxury products.
Start learning to practice all 9 FILTER Clause problems, with instant grading and mastery tracking.
Deeper guides on FILTER Clause
- one column per category with FILTER
One aggregate with a FILTER clause per column, with no extension to install.
Common questions about FILTER Clause
Does FILTER work with every aggregate?
Yes, with counts, sums, averages and the extremes alike. Each aggregate in the SELECT list carries its own FILTER, and a row excluded by one is still visible to the others, which is what lets several differently-filtered figures share one pass over the table.
Can you use FILTER together with GROUP BY?
Yes, and that is where it earns its keep. Each group gets its own filtered and unfiltered figures side by side, so one row per customer can show their total orders next to their delivered ones.
What does a FILTER that matches nothing return?
A count of zero and a sum of NULL, following the ordinary aggregate rules. The difference matters in a report, because a zero reads as a measurement and a NULL reads as a gap, and only one of them is what you meant.
