FILTER
SQL construct
Leaves only the rows that match a condition for an aggregate function.
PostgreSQL 17.5
aggregate_function(...) FILTER (WHERE condition)Parameters
- aggregate_functionname
- Aggregate function: COUNT, SUM, AVG, STRING_AGG and others
- conditionboolean
- Condition for selecting rows for this function
Return value
The result of the aggregate function over the selected rows
Examples
All and expensive payments
PostgreSQL 17.5
SELECT COUNT(*) AS all_payments,
COUNT(*) FILTER (
WHERE unit_price >= 1000
) AS expensive,
SUM(amount * unit_price) FILTER (
WHERE unit_price >= 1000
) AS expensive_total
FROM Payments;Payments by year
PostgreSQL 17.5
SELECT family_member,
COUNT(*) FILTER (
WHERE date < '2006-01-01'
) AS in_2005,
COUNT(*) FILTER (
WHERE date >= '2006-01-01'
) AS in_2006
FROM Payments
GROUP BY family_member
ORDER BY family_member;Names of children only
PostgreSQL 17.5
SELECT STRING_AGG(member_name, ', ') FILTER (
WHERE status IN ('son', 'daughter')
) AS children
FROM FamilyMembers;Details
The condition applies to one function only, so one query can compute several figures with different conditions. It is shorter than SUM(CASE WHEN ... THEN ... END), the form used in MySQL, which has no FILTER.
If no row matches, COUNT returns 0, while SUM and other functions return NULL.