FILTER Clause — Conditional Aggregation

The FILTER clause is a clean, readable alternative to the CASE WHEN ... THEN ... ELSE NULL END pattern inside aggregate functions. It restricts which rows participate in the aggregation without affecting the overall GROUP BY grouping.

The classic conditional aggregation pattern looks like this:

-- Old way (works but verbose)
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount

-- FILTER way (cleaner, same result)
SUM(amount) FILTER (WHERE status = 'paid') AS paid_amount

Both produce identical results, but FILTER is cleaner, easier to read, and communicates intent more clearly. It also works with every aggregate function — COUNT, AVG, MAX, ARRAY_AGG, STRING_AGG, and even PERCENTILE_CONT.

Example 1: Multi-status revenue breakdown in one GROUP BY

This query shows the power of FILTER for producing wide summary tables. Rather than running separate queries for each status, or using verbose CASE WHEN inside every aggregate, FILTER makes each conditional column a simple declaration.

SELECT
    c.industry,
    COUNT(*) AS total_invoices,
    COUNT(*) FILTER (WHERE i.status = 'paid')    AS paid_invoices,
    COUNT(*) FILTER (WHERE i.status = 'overdue') AS overdue_invoices,
    COUNT(*) FILTER (WHERE i.status = 'sent')    AS open_invoices,
    ROUND(SUM(ii.quantity * ii.unit_price), 0)                                  AS total_billed,
    ROUND(SUM(ii.quantity * ii.unit_price) FILTER (WHERE i.status = 'paid'), 0) AS collected,
    ROUND(SUM(ii.quantity * ii.unit_price) FILTER (WHERE i.status = 'overdue'), 0) AS overdue_balance,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE i.status = 'paid')::NUMERIC / COUNT(*),
        1
    ) AS collection_rate_pct
FROM clients c
JOIN invoices i ON i.client_id = c.client_id
JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
GROUP BY c.industry
ORDER BY total_billed DESC;

What This Returns

A single row per industry with every financial dimension side by side. This is the query that powers an AR dashboard — one GROUP BY, one scan of the tables, and you have every KPI you need.

Example 2: FILTER with COUNT DISTINCT and AVG

FILTER works with any aggregate, including COUNT(DISTINCT ...) and AVG. This makes it easy to compute conditional averages and unique counts in one pass:

SELECT
    c.acquisition_channel,
    COUNT(DISTINCT c.customer_id) AS total_customers,
    COUNT(DISTINCT c.customer_id) FILTER (WHERE DATE_TRUNC('day', o.created_at)::DATE >= CURRENT_DATE - 90)
        AS active_last_90d,
    ROUND((AVG(o.total_amount))::NUMERIC, 2)
        AS overall_avg_order,
    ROUND(AVG(o.total_amount) FILTER (WHERE o.total_amount > 100), 2)
        AS avg_order_over_100,
    ROUND(AVG(o.total_amount) FILTER (WHERE o.promo_code IS NOT NULL), 2)
        AS avg_promo_order,
    ROUND(AVG(o.total_amount) FILTER (WHERE o.promo_code IS NULL), 2)
        AS avg_non_promo_order,
    COUNT(*) FILTER (WHERE o.promo_code IS NOT NULL) AS promo_orders,
    COUNT(*) FILTER (WHERE o.status = 'refunded')   AS refunds
FROM ec_customers c
LEFT JOIN ec_orders o ON o.customer_id = c.customer_id AND o.status != 'cancelled'
GROUP BY c.acquisition_channel
ORDER BY total_customers DESC;

What This Returns

This single query answers five different "what is the average order value for X subset?" questions simultaneously. Notice that FILTER on AVG correctly excludes NULL from the denominator — it averages only the rows that pass the filter condition, not counting the excluded rows as zeros.

Example 3: FILTER with window functions

FILTER also works inside window function calls — a less commonly known feature that opens up powerful conditional running totals and rolling counts:

SELECT
    DATE_TRUNC('day', o.created_at)::DATE,
    o.order_id,
    c.acquisition_channel,
    o.total_amount,
    -- Running total for this channel only (using FILTER in window function)
    SUM(o.total_amount) FILTER (WHERE c.acquisition_channel = 'organic')
        OVER (ORDER BY DATE_TRUNC('day', o.created_at)::DATE) AS organic_running_total,
    -- Count of promo orders up to this date
    COUNT(*) FILTER (WHERE o.promo_code IS NOT NULL)
        OVER (ORDER BY DATE_TRUNC('day', o.created_at)::DATE ROWS UNBOUNDED PRECEDING) AS promo_orders_to_date,
    -- 7-day count of refunds
    COUNT(*) FILTER (WHERE o.status = 'refunded')
        OVER (ORDER BY DATE_TRUNC('day', o.created_at)::DATE ROWS 6 PRECEDING) AS refunds_last_7d
FROM ec_orders o
JOIN ec_customers c ON c.customer_id = o.customer_id
ORDER BY DATE_TRUNC('day', o.created_at)::DATE;

Example 4: FILTER for cohort metrics in a single pass

One of the most powerful applications: computing cohort-level metrics all in one GROUP BY without subqueries:

SELECT
    DATE_TRUNC('month', c.created_at)::DATE AS cohort_month,
    COUNT(DISTINCT c.customer_id) AS cohort_size,
    -- Customers who ordered within 30 days (activation)
    COUNT(DISTINCT c.customer_id) FILTER (
        WHERE o.first_order_date <= c.created_at::DATE + 30
    ) AS activated_30d,
    -- Customers who placed 3+ orders (engaged)
    COUNT(DISTINCT c.customer_id) FILTER (
        WHERE o.order_count >= 3
    ) AS highly_engaged,
    -- Revenue from high-value orders only
    ROUND(SUM(o.total_amount) FILTER (WHERE o.total_amount > 200), 2) AS high_value_revenue,
    -- Average time to first order (only for customers who ordered)
    ROUND(AVG(
        ((o.first_order_date)::DATE - c.created_at::DATE)::NUMERIC
    ) FILTER (WHERE o.first_order_date IS NOT NULL), 1) AS avg_days_to_first_order
FROM ec_customers c
LEFT JOIN (
    SELECT customer_id,
        MIN(created_at) AS first_order_date,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_amount
    FROM ec_orders WHERE status = 'completed'
    GROUP BY customer_id
) o ON o.customer_id = c.customer_id
GROUP BY DATE_TRUNC('month', c.created_at)
ORDER BY cohort_month;

Key Takeaway

The FILTER clause is the most underused quality-of-life improvement in PostgreSQL aggregation. It replaces CASE WHEN ... ELSE NULL END with a clean, readable WHERE condition on any aggregate function. Use it to build wide summary tables from a single GROUP BY — every "what percentage of X meets condition Y?" question can be answered with COUNT(*) FILTER (WHERE condition) / COUNT(*). It works with window functions too, enabling conditional running totals.