SUM and AVG over INTERVAL data types

Version

23.2
Categories

SQL

You can pass INTERVAL datatypes to the SUM and AVG aggregate and analytic functions.

with rws as (
  select interval '0' hour + numtodsinterval ( level, 'hour' ) dsi
  connect by level <= 10
)
  select sum ( dsi ) total_duration,
         avg ( dsi ) mean_duration
  from   rws;
Result
SQL> with rws as (
  2    select interval '0' hour + numtodsinterval ( level, 'hour' ) dsi
  3      connect by level <= 10
  4  )
  5    select sum ( dsi ) total_duration,
  6           avg ( dsi ) mean_duration
  7    from   rws;

TOTAL_DURATION      MEAN_DURATION
------------------- -------------------
+02 07:00:00.000000 +00 05:30:00.000000

Benefits

This enhancement makes it easier for developers to calculate totals and averages over INTERVAL values.

Further information