FILTER

---

FILTER (The "Clean" Conditional Aggregate)

The FILTER clause is used with aggregate functions (like COUNT, SUM, or AVG) to specify which rows should be included in the calculation.

Before FILTER, we had to use clunky CASE statements inside aggregates. FILTER makes the intent much clearer.

• Syntax: AGGREGATE_FUNCTION(...) FILTER (WHERE condition)

• Best for: Creating pivot-style reports or comparing subsets of data in a single row.

Example: Suppose you want to see total sales and "VIP" sales side-by-side:

SELECT 
    region,
    SUM(amount) AS total_sales,
    SUM(amount) FILTER (WHERE customer_type = 'VIP') AS vip_sales
FROM sales
GROUP BY region;

---