Overview
The FILTER clause is a nice addition to the SQL syntax in Oracle 26ai (23.26.1). It allows to restrict the rows considered by aggregate functions.
An aggregate query divides a set of records into groups, and returns a single result row for each group.
By default, the input for every aggregate function in the query includes all the records of the group.
However, sometimes we want the input to a specific aggregate function in the query to be only a subset of the group’s records. The FILTER clause provides an elegant solution for this requirement.
Example
As an example, I’ll use the EUROVISION_WINNERS table that I created for my Pattern Matching presentation.
This table contains a record for every year of the Eurovision Song Contest, with some details about the winner of that year.
SQL> select * from eurovision_winners order by year;
I want to analyze the languages of the winning songs. For each language, I’d like to get: Continue reading “Aggregation Filters”