FILTER Clause

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Filters which rows are passed to an aggregate function based on a condition.

1<aggregate_function>(<expression>) FILTER (WHERE <condition>)

• <aggregate_function>: Any aggregate function.
• <condition>: A boolean expression that determines which rows to include.

Returns the result of the aggregate function applied only to rows where the condition is true.

• Only rows where the condition evaluates to true are passed to the aggregate.
• Normal rules for empty groups apply: aggregates with no matching rows return NULL.
• Exceptions: count(*) and count(expression) return 0 for empty groups.
• Allows computing multiple filtered aggregations in a single query.
• ARRAY_AGG and JSON_ARRAYAGG don’t support FILTER.

Count total rows and filtered rows.

1SELECT
2    count(*) AS total_cases,
3    count(*) FILTER (WHERE color = 'blue') AS blue_cases
4FROM phone_cases;

Returns:

Calculate different aggregates with different filters.

1SELECT
2    color,
3    avg(price) AS avg_price,
4    avg(price) FILTER (WHERE model = 'sfPhone') AS avg_sfphone_price,
5    count(*) FILTER (WHERE model = 'sfPhone') AS sfphone_count
6FROM phone_cases
7GROUP BY color;

Returns:

Sum values matching a condition.

1SELECT
2    sum(amount) AS total_sales,
3    sum(amount) FILTER (WHERE region = 'West') AS west_sales,
4    sum(amount) FILTER (WHERE region = 'East') AS east_sales
5FROM sales;

• Aggregate Functions
• GROUP BY Clause