Aggregation Filter in Oracle AI 26 ai

Aggregation filter is yet another neat addition to the SQL in Oracle AI 26 ai. This feature makes the SQL concise , easier to read.

I have to be honest . It looks different though. Probably , I have to get used to this. :-) . Here is an example.

SQL> select banner from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production

SQL> DROP TABLE IF EXISTS t;

Table dropped.

  1  CREATE TABLE t
  2      AS
  3          SELECT
  4              object_name,
  5              object_type
  6          FROM
  7*           all_objects
SQL> /

Table created.

I am using “Drop table if exists” clause ( another new feature in 26 ai) .

Here is the newer ( simpler ) syntax in Oracle AI 26ai.

  1  SELECT
  2      COUNT(*) filter ( Where object_type = 'TABLE' ) as tablecount ,
  3      count(*) filter ( Where object_type = 'VIEW' )   as viewcount
  4      from
  5*         t
SQL> /

TABLECOUNT  VIEWCOUNT
---------- ----------
       142       2288

In the pre-oracle ai 26 ai release , we typically get these results via one of the following scripts below.

Method 1 :

 1  select sum(
  2      case
  3          when object_type = 'TABLE' then
  4              1
  5          else
  6              0
  7      end
  8  ) as tablecount,
  9      sum(
 10          case
 11              when object_type = 'VIEW' then
 12                  1
 13              else
 14                  0
 15          end
 16      ) as viewcount
 17* from t;
 18  /

TABLECOUNT  VIEWCOUNT
---------- ----------
       142       2288

Method 2:

  1  SELECT
  2      COUNT(decode(object_type, 'TABLE', 1)) AS tablecount,
  3      COUNT(decode(object_type, 'VIEW', 1))  AS viewcount
  4  FROM
  5*     t
SQL> /

TABLECOUNT  VIEWCOUNT
---------- ----------
       142       2288

Originally published at https://zahirmohideen.blogspot.com/2026/02/aggregation-filter-in-oracle-ai-26-ai.html

Updated to show the additional script.