PostgreSQL 18 reference · Aggregate Functions

PostgreSQL FILTER Clause for Aggregates

Restrict which rows one aggregate sees with FILTER (WHERE …), so one query can count several conditions.

PostgreSQL 18 Since 9.4

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

aggregate(...) FILTER (WHERE condition)

Parameters

Parameter	Description
`condition`	Only rows where it is true are aggregated.

Return value

The aggregate's result.

Examples

Several counts at once

SELECT count(*) AS all_orders,
       count(*) FILTER (WHERE status = 'delivered') AS delivered,
       count(*) FILTER (WHERE status = 'pending') AS pending
FROM orders;

Output

 all_orders | delivered | pending
------------+-----------+---------
         12 |         7 |       2
(1 row)

Run this on your computer — see “Get Started” for setup.

A filtered average

SELECT department,
       round(avg(salary) FILTER (WHERE hire_date >= DATE '2022-01-01')) AS avg_recent_hires
FROM employees
GROUP BY department
ORDER BY department;