# `FILTER` Clause

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

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

## Syntax

```sql
<aggregate_function>(<expression>) FILTER (WHERE <condition>)
```

## Arguments

### Required

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

## Returns

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

## Considerations

- 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`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-agg.md) and [`JSON_ARRAYAGG`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/json-arrayagg.md) don't support `FILTER`.

## Examples

### Count with Filter

Count total rows and filtered rows.

```sql
SELECT
    count(*) AS total_cases,
    count(*) FILTER (WHERE color = 'blue') AS blue_cases
FROM phone_cases;
```

Returns:

| total_cases | blue_cases |
| :---------- | :--------- |
| 4           | 2          |

### Multiple Filtered Aggregates

Calculate different aggregates with different filters.

```sql
SELECT
    color,
    avg(price) AS avg_price,
    avg(price) FILTER (WHERE model = 'sfPhone') AS avg_sfphone_price,
    count(*) FILTER (WHERE model = 'sfPhone') AS sfphone_count
FROM phone_cases
GROUP BY color;
```

Returns:

| color | avg_price | avg_sfphone_price | sfphone_count |
| :---- | :-------- | :---------------- | :------------ |
| black | 50        | 50                | 1             |
| blue  | 30        | 45                | 1             |
| red   | 17        | NULL              | 0             |

### Filter with SUM

Sum values matching a condition.

```sql
SELECT
    sum(amount) AS total_sales,
    sum(amount) FILTER (WHERE region = 'West') AS west_sales,
    sum(amount) FILTER (WHERE region = 'East') AS east_sales
FROM sales;
```

## Related Documentation

- [Aggregate Functions](/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.md)
- [GROUP BY Clause](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-group-by.md)