FILTER

FILTER is a clause in SQL that is used to include conditions in aggregate functions. This command allows the user to calculate the aggregate function over a specific subset of rows rather than the entire set of rows.

Example

SELECT
    COUNT(*) FILTER (WHERE age > 18) AS "number_of_adults",
    COUNT(*) FILTER (WHERE age <= 18) AS "number_of_children"
FROM
    people;

Output

 number_of_adults | number_of_children
------------------+--------------------
      25          |        10

Explanation

The example code counts the number of adults (individuals older than 18 years) and children (individuals 18 years or younger) in the table named people. The FILTER clause specifies the condition based on which the rows are counted.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.