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.
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;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