Optimizing Queries with FILTER and Conditional Aggregation
Learn how the FILTER clause and CASE-based conditional aggregation let you compute multiple metrics in a single table pass instead of running several separate queries.
Optimizing Queries with FILTER and Conditional Aggregation is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Problem: Many Counts, One Table
Dashboards often need several metrics from the same table — total orders, paid orders, refunded orders. Running three separate queries scans the table three times. We can do it in one pass.
Conditional Aggregation with CASE
The classic trick wraps a CASE inside an aggregate. Rows that do not match contribute NULL, which COUNT and SUM ignore.
SELECT
COUNT(*) AS total,
COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid
FROM orders;The Cleaner FILTER Clause
PostgreSQL offers a more readable form: the FILTER clause attached to any aggregate. It expresses intent directly.
SELECT
COUNT(*) AS total,
COUNT(*) FILTER (WHERE status = 'paid') AS paid,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;Why This Is Faster
All metrics are computed in a single scan of the table. The planner reads each row once and updates every aggregate, instead of scanning the table separately for each metric.
FILTER with SUM and AVG
FILTER works with any aggregate, not just COUNT. Compute conditional sums and averages in the same query.
SELECT
SUM(total) FILTER (WHERE status = 'paid') AS revenue,
AVG(total) FILTER (WHERE status = 'paid') AS avg_paid
FROM orders;Combining with GROUP BY
FILTER shines inside grouped queries, producing a pivot-like result with one row per group and several conditional columns.
SELECT
region,
COUNT(*) FILTER (WHERE status = 'paid') AS paid,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
GROUP BY region;Pivoting Months into Columns
A common report turns rows into columns. FILTER makes a clean monthly pivot without extension functions.
SELECT
product_id,
SUM(total) FILTER (WHERE month = 1) AS jan,
SUM(total) FILTER (WHERE month = 2) AS feb
FROM sales
GROUP BY product_id;Reading the Plan
EXPLAIN ANALYZE confirms a single Aggregate node over one scan. Compare it against three separate queries to see the saved scans.
EXPLAIN ANALYZE
SELECT
COUNT(*) FILTER (WHERE status = 'paid') AS paid,
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;FILTER vs WHERE
Remember the difference:
- WHERE removes rows before any aggregate sees them
- FILTER keeps all rows but restricts which ones a specific aggregate counts
Use FILTER when different aggregates need different conditions.
Combining with Indexes
If most metrics target a subset (e.g. only recent rows), add a WHERE for the shared condition so an index narrows the scan, then use FILTER for the per-metric splits.
SELECT
COUNT(*) FILTER (WHERE status = 'paid') AS paid
FROM orders
WHERE created_at >= now() - interval '30 days';Counting Distinct Conditionally
FILTER also pairs with COUNT(DISTINCT ...), letting you count unique customers per status in one scan instead of several grouped queries.
SELECT
COUNT(DISTINCT customer_id) FILTER (WHERE status = 'paid') AS paying_customers
FROM orders;Quick Check
Test your conditional aggregation knowledge.
Recap
You learned conditional aggregation:
- Compute many metrics in one scan with FILTER or CASE
- FILTER is more readable and works with any aggregate
- Combine with GROUP BY for pivot-style reports
- WHERE removes rows; FILTER restricts a single aggregate
- Add a shared WHERE so indexes narrow the scan
Frequently asked questions
Is the “Optimizing Queries with FILTER and Conditional Aggregation” lesson free?
Yes — the full text of “Optimizing Queries with FILTER and Conditional Aggregation” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.
What will I learn in “Optimizing Queries with FILTER and Conditional Aggregation”?
Learn how the FILTER clause and CASE-based conditional aggregation let you compute multiple metrics in a single table pass instead of running several separate queries. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start PostgreSQL Performance & Query Optimization?
No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Optimizing Queries with FILTER and Conditional Aggregation” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?
Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Optimizing Aggregates and Window Functions
- Recursive CTEs and Graph Queries
- Using Materialized Views for Performance
- Optimizing Queries with FILTER and Conditional Aggregation