aggregate

Aggregates With FILTER (Conditional Aggregation)

Count completed vs cancelled orders in one pass using FILTER clauses.

Overview

DuckDB (and Postgres) support a FILTER (WHERE …) clause on aggregates, letting you compute conditional aggregates in a single GROUP BY query — no CASE required. Here we count completed and cancelled orders per product category side by side.

The Query

Store (Customers/Orders/Products)

Loading database engine...

Step-by-Step Breakdown

COUNT(*) FILTER (WHERE o.status = 'completed') — counts only completed orders in each category.
COUNT(*) FILTER (WHERE o.status = 'cancelled') — counts only cancelled ones.
Both share the same GROUP BY p.category, so they align per category.

FILTER vs CASE

COUNT(*) FILTER (WHERE …) is equivalent to COUNT(CASE WHEN … THEN 1 END) but reads more cleanly and is often faster.

Variations

Average salary of active vs all employees per department:

Employees & Departments

Loading database engine...

Common Mistakes

Using FILTER where unsupported. MySQL lacks FILTER; fall back to CASE. Know your engine.
Forgetting the aggregate. FILTER modifies an aggregate; you can't write col FILTER (WHERE …) on a bare column.
Doubling counts. COUNT(*) FILTER still respects the GROUP BY; don't also add a WHERE status = … that drops the other rows you wanted to count.

Related examples

Core Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

aggregate

A single query demonstrating all five core aggregates over the employees table.

GROUP BY with HAVING

group by

Use HAVING to keep only departments whose average salary exceeds a threshold after aggregation.