---
title: "FILTER clause"
description: "The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions."
canonical: "https://motherduck.com/glossary/filter-clause/"
related:
  - title: "Query syntax | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/query-syntax/"
  - title: "Why Use DuckDB for Analytics?"
    url: "https://motherduck.com/blog/six-reasons-duckdb-slaps/"
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
---

# FILTER clause

> The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions.

## Overview
The standard SQL `FILTER (WHERE condition)` clause attaches a row filter directly to an aggregate function call, so a single query can compute several differently-scoped aggregates side by side:

```sql
SELECT
  region,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
  SUM(revenue) FILTER (WHERE channel = 'online') AS online_revenue
FROM orders
GROUP BY region;
```

<glossary-callout guide="duckdb-cheatsheet-full" />

## FILTER vs CASE WHEN
Before `FILTER` was widely supported, the same result required wrapping the aggregate's argument in a `CASE` expression:

```sql
SELECT
  region,
  SUM(CASE WHEN channel = 'online' THEN revenue ELSE NULL END) AS online_revenue
FROM orders
GROUP BY region;
```

`FILTER` is more readable and makes intent explicit — the condition governs which rows are counted or summed, not what value is substituted. It also composes cleanly with `COUNT(*)`, where a `CASE` expression would need an awkward `THEN 1 ELSE NULL END` pattern.

## DuckDB specifics
DuckDB fully supports `FILTER (WHERE ...)` on any aggregate function, including with `GROUP BY ALL`:

```sql
SELECT
  region,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY ALL;
```

DuckDB also allows `FILTER` on window function aggregates (e.g., `SUM(x) FILTER (WHERE ...) OVER (...)`), which is a more advanced combination not all SQL engines support, letting you compute a filtered running total in one pass.