Conditional aggregation applies conditions within aggregate functions to count or sum only rows matching specific criteria, creating pivot-style reports in single queries.

**Core Concept:**

Apply different conditions to same data within aggregates. Get multiple metrics based on different conditions - all in one query.

**CASE Inside Aggregates:**

- SUM(CASE WHEN condition THEN 1 ELSE 0 END) - Counts matching rows

- SUM(CASE WHEN condition THEN amount ELSE 0 END) - Sums matching values

Example:

SELECT

department,

SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,

SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count

FROM employees

GROUP BY department;

**FILTER Syntax (PostgreSQL):**

- COUNT(*) FILTER (WHERE condition)

- SUM(amount) FILTER (WHERE condition)

More readable but limited database support.

**CASE vs FILTER:**

- CASE: Works everywhere, more verbose

- FILTER: Cleaner, PostgreSQL/SQL Server 2022+

Essential for analytics engineers and BI developers. Appears in data interviews as "pivot in SQL" questions. Critical for multi-metric dashboards.

Powers status breakdowns, funnel analysis, and pivot reports. E-commerce shows orders by status, SaaS tracks active/churned/trial users, product teams analyze feature usage patterns.