Skip to content
SQLSimplified
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

Loading playground environment...

Step-by-Step Breakdown

  1. COUNT(*) FILTER (WHERE o.status = 'completed'): counts only completed orders in each category.
  2. COUNT(*) FILTER (WHERE o.status = 'cancelled'): counts only cancelled ones.
  3. 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:

Loading playground environment...

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.

Cite this resource

SQLSimplified. "Aggregates With FILTER (Conditional Aggregation) Example". Available at: https://sqlsimplified.online/examples/aggregate-with-filter