Conditional Aggregation

What is Conditional Aggregation?

Conditional aggregation computes aggregate values based on specific conditions within groups. It allows you to calculate multiple metrics in a single pass over the data, creating pivot-table-like output.

Syntax

Using FILTER (PostgreSQL):

SELECT
  category,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE price > 100) AS expensive_count,
  AVG(price) FILTER (WHERE price > 0) AS avg_positive_price
FROM products
GROUP BY category;

Using CASE (standard SQL):

SELECT
  category,
  COUNT(*) AS total,
  SUM(CASE WHEN price > 100 THEN 1 ELSE 0 END) AS expensive_count
FROM products
GROUP BY category;

When to Use

• Building dashboard metrics in a single query
• Creating cross-tab or pivot reports
• Computing pass/fail rates, percentages, or status breakdowns per group

Key Points

1. Single Scan — All conditional aggregates are computed in one table scan.
2. FILTER vs CASE — Both achieve the same result. FILTER is PostgreSQL-specific but more readable.
3. Percentages — Compute percentages: COUNT(*) FILTER (WHERE x)::float / COUNT(*).
4. GROUP BY — Conditional aggregation is most powerful combined with GROUP BY.
5. Readability — Name each aggregate column clearly with AS.