The new FILTER clause in Aggregations

 Oracle 26.1 introduces the new FILTER clause that can be used in aggregations.  

Thus, for example, in a SUM aggregation you can use FILTER WHERE ...

Here is a quick example :

SQL> create table obj_list as select * from dba_objects;

Table created.

SQL> select sum(object_id) from obj_list;

SUM(OBJECT_ID)
--------------
    2604221816

SQL> select object_type, sum(object_id) from obj_list group by object_type order by 1;

OBJECT_TYPE             SUM(OBJECT_ID)
----------------------- --------------
CLUSTER                           3036
CONSUMER GROUP                  435231
CONTEXT                        1048288
...... deleted a number of object types
SEQUENCE                       7551346
SYNONYM                      473741064
TABLE                         44661426   --> sum of object_ids for object_type TABLE
TABLE PARTITION               15047252
TABLE SUBPARTITION              603760
...... deleted a few more object types
XML SCHEMA                      511732

46 rows selected.


SQL> select sum(object_id) filter (where object_type = 'TABLE')  as Table_ObjID_Sum from obj_list;

TABLE_OBJID_SUM
---------------
       44661426


SQL>

For more information see : 

1. Oracle Documentation New Features Guide: Aggregation Filters

2. Oracle Documentation SQL For Application Developers : 14.2 Aggregation Filters 

3. Markus Winand's Modern SQL Reference : Filter