-- 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 );