PostgreSQL FILTER clause for conditional aggregation

Conditional counting/summing requires CASE WHEN inside aggregate functions, which is verbose.

Use the FILTER clause for cleaner conditional aggregates:

SELECT
count(*) AS total_orders,
count(*) FILTER (WHERE status = 'completed') AS completed,
count(*) FILTER (WHERE status = 'pending') AS pending,
sum(amount) FILTER (WHERE status = 'completed') AS completed_revenue,
avg(amount) FILTER (WHERE created_at > now() - interval '30 days') AS recent_avg
FROM orders;

-- Equivalent CASE WHEN (more verbose):
-- count(CASE WHEN status = 'completed' THEN 1 END) AS completed

FILTER is PostgreSQL-specific but far more readable than CASE WHEN for conditional aggregation.