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;
YEAR WINNER SONG PERFORMER POINTS LANGUAGE HOST
---------- --------------- ----------------------------------- -------------------------------------- ---------- ------------- --------------
1956 Switzerland Refrain Lys Assia French Switzerland
1957 Netherlands Net als toen Corry Brokken 31 Dutch Germany
1958 France Dors, mon amour André Claveau 27 French Netherlands
1959 Netherlands Een beetje Teddy Scholten 21 Dutch France
1960 France Tom Pillibi Jacqueline Boyer 32 French United Kingdom
1961 Luxembourg Nous les amoureux Jean-Claude Pascal 31 French France
1962 France Un premier amour Isabelle Aubret 26 French Luxembourg
1963 Denmark Dansevise Grethe and Jørgen Ingmann 42 Danish United Kingdom
1964 Italy Non ho l'età Gigliola Cinquetti 49 Italian Denmark
1965 Luxembourg Poupée de cire, poupée de son France Gall 32 French Italy
1966 Austria Merci, Chérie Udo Jürgens 31 German Luxembourg
1967 United Kingdom Puppet on a String Sandie Shaw 47 English Austria
1968 Spain La, la, la Massiel 29 Spanish United Kingdom
1969 Netherlands De troubadour Lenny Kuhr 18 Dutch Spain
1970 Ireland All Kinds of Everything Dana 32 English Netherlands
1971 Monaco Un banc, un arbre, une rue Séverine 128 French Ireland
1972 Luxembourg Après toi Vicky Leandros 128 French United Kingdom
1973 Luxembourg Tu te reconnaîtras Anne-Marie David 129 French Luxembourg
1974 Sweden Waterloo ABBA 24 English United Kingdom
1975 Netherlands Ding-a-dong Teach-In 152 English Sweden
1976 United Kingdom Save Your Kisses for Me Brotherhood of Man 164 English Netherlands
1977 France L'oiseau et l'enfant Marie Myriam 136 French United Kingdom
1978 Israel A-Ba-Ni-Bi Izhar Cohen and the Alphabeta 157 Hebrew France
1979 Israel Hallelujah Gali Atari and Milk and Honey 125 Hebrew Israel
1980 Ireland What's Another Year Johnny Logan 143 English Netherlands
1981 United Kingdom Making Your Mind Up Bucks Fizz 136 English Ireland
1982 Germany Ein bißchen Frieden Nicole 161 German United Kingdom
1983 Luxembourg Si la vie est cadeau Corinne Hermès 142 French Germany
1984 Sweden Diggi-Loo Diggi-Ley Herreys 145 Swedish Luxembourg
1985 Norway La det swinge Bobbysocks! 123 Norwegian Sweden
1986 Belgium J'aime la vie Sandra Kim 176 French Norway
1987 Ireland Hold Me Now Johnny Logan 172 English Belgium
1988 Switzerland Ne partez pas sans moi Céline Dion 137 French Ireland
1989 Yugoslavia Rock Me Riva 137 Croatian Switzerland
1990 Italy Insieme: 1992 Toto Cutugno 149 Italian Yugoslavia
1991 Sweden Fångad av en stormvind Carola 146 Swedish Italy
1992 Ireland Why Me? Linda Martin 155 English Sweden
1993 Ireland In Your Eyes Niamh Kavanagh 187 English Ireland
1994 Ireland Rock 'n' Roll Kids Paul Harrington and Charlie McGettigan 226 English Ireland
1995 Norway Nocturne Secret Garden 148 Norwegian Ireland
1996 Ireland The Voice Eimear Quinn 162 English Norway
1997 United Kingdom Love Shine a Light Katrina and the Waves 227 English Ireland
1998 Israel Diva Dana International 172 Hebrew United Kingdom
1999 Sweden Take Me to Your Heaven Charlotte Nilsson 163 English Israel
2000 Denmark Fly on the Wings of Love Olsen Brothers 195 English Sweden
2001 Estonia Everybody Tanel Padar, Dave Benton and 2XL 198 English Denmark
2002 Latvia I Wanna Marie N 176 English Estonia
2003 Turkey Everyway That I Can Sertab Erener 167 English Latvia
2004 Ukraine Wild Dances Ruslana 280 Ukrainian Turkey
2005 Greece My Number One Helena Paparizou 230 English Ukraine
2006 Finland Hard Rock Hallelujah Lordi 292 English Greece
2007 Serbia Molitva Marija Šerifovic 268 Serbian Finland
2008 Russia Believe Dima Bilan 272 English Serbia
2009 Norway Fairytale Alexander Rybak 387 English Russia
2010 Germany Satellite Lena 246 English Norway
2011 Azerbaijan Running Scared Ell & Nikki 221 English Germany
2012 Sweden Euphoria Loreen 372 English Azerbaijan
2013 Denmark Only Teardrops Emmelie de Forest 281 English Sweden
2014 Austria Rise Like a Phoenix Conchita Wurst 290 English Denmark
2015 Sweden Heroes Måns Zelmerlöw 365 English Austria
2016 Ukraine 1944 Jamala 534 Crimean Tatar Sweden
2017 Portugal Amar pelos dois Salvador Sobral 758 Portuguese Ukraine
2018 Israel Toy Netta Barzilai 529 English Portugal
2019 Netherlands Arcade Duncan Laurence 498 English Israel
2021 Italy Zitti e buoni M†neskin 524 Italian Netherlands
2022 Ukraine Stefania Kalush Orchestra 631 Ukrainian Italy
2023 Sweden Tattoo Loreen 583 English United Kingdom
2024 Switzerland The Code Nemo 591 English Sweden
2025 Austria Wasted Love JJ 436 English Switzerland
69 rows selected.
I want to analyze the languages of the winning songs. For each language, I’d like to get: Continue reading “Aggregation Filters”