FirebirdSQL logo
 Built-in Scalar FunctionsWindow (Analytical) Functions 

FILTER Clause for Aggregate Functions

The FILTER clause extends aggregate functions (SUM, AVG, COUNT, etc.) with an additional WHERE clause.This limits the rows processed by the aggregate functions to the rows that satisfy the conditions of both the main WHERE clause and those inside the FILTER clause.

It can be thought of as a more explicit form of using an aggregate function with a condition (DECODE, CASE, IIF, NULLIF) to ignore some values that would otherwise be considered by the aggregation.

The FILTER clause can be used with any aggregate functions in aggregate or windowed (OVER) statements, but not with window-only functions like DENSE_RANK.

Example of FILTER

Suppose you need a query to count the rows with status = 'A' and the row with status = 'E' as different columns.The old way to do it would be:

select count(decode(status, 'A', 1)) status_a,
       count(decode(status, 'E', 1)) status_e
from data;

The FILTER clause lets you express those conditions more explicitly:

select count(*) filter (where status = 'A') status_a,
       count(*) filter (where status = 'E') status_e
from data;
Tip

You can use more than one FILTER modifier in an aggregate query.You could, for example, use 12 filters on totals aggregating sales for a year to produce monthly figures for a pivot set.

General-purpose Aggregate Functions