-- AVG and SUM aggregate functions for INTERVAL DAY TO SECOND values.
-- William Robertson 2004, www.williamrobertson.net
--
-- Since introducing the interval types in 9i, Oracle has neglected to overload the
-- aggregate functions SUM, AVG etc for them:
--
-- SQL> SELECT SUM(INTERVAL '1' SECOND) FROM dual
-- SELECT SUM(INTERVAL '1' SECOND) FROM dual
-- *
-- ERROR at line 1:
-- ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
--
-- Until they get around to it, you can create your own:
--
-- SQL> SELECT SUM_DSINTERVAL(INTERVAL '1' SECOND) FROM dual;
--
-- SUM_DSINTERVAL(INTERVAL'1'SECOND)
-- ---------------------------------------------------------------------------
-- +000000000 00:00:01.000000000
--
-- 1 row selected.


CREATE OR REPLACE TYPE dsinterval_avg AS OBJECT
( total INTERVAL DAY TO SECOND
, items INTEGER
, STATIC FUNCTION ODCIAggregateInitialize
( sctx IN OUT dsinterval_avg )
RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateIterate
( SELF IN OUT dsinterval_avg
, value IN INTERVAL DAY TO SECOND )
RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateTerminate
( SELF IN dsinterval_avg
, returnvalue OUT INTERVAL DAY TO SECOND
, flags IN NUMBER )
RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateMerge
( SELF IN OUT dsinterval_avg
, ctx2 IN dsinterval_avg )
RETURN NUMBER
);
/

show errors

CREATE OR REPLACE TYPE BODY dsinterval_avg
AS
STATIC FUNCTION ODCIAggregateInitialize
( sctx IN OUT dsinterval_avg )
RETURN NUMBER
IS
BEGIN
sctx := NEW dsinterval_avg(INTERVAL '0' SECOND, 0);
RETURN ODCIConst.Success;
END;


MEMBER FUNCTION ODCIAggregateIterate
( SELF IN OUT dsinterval_avg
, value IN INTERVAL DAY TO SECOND )
RETURN number
IS
BEGIN
SELF.total := SELF.total + value;
SELF.items := SELF.items + 1;
RETURN ODCIConst.Success;
END;


MEMBER FUNCTION ODCIAggregateTerminate
( SELF IN dsinterval_avg
, returnValue OUT INTERVAL DAY TO SECOND
, flags IN NUMBER )
RETURN NUMBER
IS
BEGIN
returnvalue := SELF.total / SELF.items;
RETURN ODCIConst.Success;
END;


MEMBER FUNCTION ODCIAggregateMerge
( SELF IN OUT dsinterval_avg
, ctx2 IN dsinterval_avg )
RETURN NUMBER
IS
BEGIN
-- The ODCIAggregateMerge function is used to combine results of
-- parallel queries. Presumably it is applied prior to DCIAggregateTerminate,
-- in which case it should probably combine totals from each thread.
-- Needs testing.

SELF.total := SELF.total + ctx2.total;
SELF.items := SELF.items + ctx2.items;

RETURN ODCIConst.Success;
END;
END;
/

show errors

-- Corresponding SUM aggregate:

CREATE OR REPLACE TYPE dsinterval_sum AS OBJECT
( total INTERVAL DAY TO SECOND
, STATIC FUNCTION ODCIAggregateInitialize
( sctx IN OUT dsinterval_sum )
RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateIterate
( SELF IN OUT dsinterval_sum
, value IN INTERVAL DAY TO SECOND )
RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateTerminate
( SELF IN dsinterval_sum
, returnvalue OUT INTERVAL DAY TO SECOND
, flags IN NUMBER )
RETURN NUMBER
, MEMBER FUNCTION ODCIAggregateMerge
( SELF IN OUT dsinterval_sum
, ctx2 IN dsinterval_sum )
RETURN NUMBER
);
/

show errors

CREATE OR REPLACE TYPE BODY dsinterval_sum
AS
STATIC FUNCTION ODCIAggregateInitialize
( sctx IN OUT dsinterval_sum )
RETURN NUMBER
IS
BEGIN
sctx := NEW dsinterval_sum(INTERVAL '0' SECOND);
RETURN ODCIConst.Success;
END;


MEMBER FUNCTION ODCIAggregateIterate
( SELF IN OUT dsinterval_sum
, value IN INTERVAL DAY TO SECOND )
RETURN number
IS
BEGIN
SELF.total := SELF.total + value;
RETURN ODCIConst.Success;
END;


MEMBER FUNCTION ODCIAggregateTerminate
( SELF IN dsinterval_sum
, returnValue OUT INTERVAL DAY TO SECOND
, flags IN NUMBER )
RETURN NUMBER
IS
BEGIN
returnvalue := SELF.total;
RETURN ODCIConst.Success;
END;


MEMBER FUNCTION ODCIAggregateMerge
( SELF IN OUT dsinterval_sum
, ctx2 IN dsinterval_sum )
RETURN NUMBER
IS
BEGIN
-- The ODCIAggregateMerge function is used to combine results of
-- parallel queries. Presumably it is applied prior to DCIAggregateTerminate,
-- in which case it should probably combine totals from each thread.
-- Needs testing.

SELF.total := SELF.total + ctx2.total;

RETURN ODCIConst.Success;
END;
END;
/

show errors

CREATE OR REPLACE FUNCTION sum_dsinterval
( input INTERVAL DAY TO SECOND )
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE
AGGREGATE USING dsinterval_sum;
/

SELECT SUM_DSINTERVAL(i) FROM ( SELECT t2 - t1 AS i FROM tstamp );


CREATE OR REPLACE FUNCTION avg_dsinterval
( input INTERVAL DAY TO SECOND )
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE
AGGREGATE USING dsinterval_avg;
/

SELECT AVG_DSINTERVAL(i) FROM ( SELECT t2 - t1 AS i FROM tstamp );