avg does not support DATEs as arguments:
sokrates@11.2 > select avg(created) from dba_users;
select avg(created) from dba_users
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
Why ? I don’t know
sokrates@11.2 > select
2 min( created ),
3 max( created ),
4 median( created )
5 from dba_users;
MIN(CREATED) MAX(CREATED) MEDIAN(CREATED)
-------------------- -------------------- --------------------
17-sep-2010 16:08:04 04-apr-2011 11:13:54 09-oct-2010 15:57:09
work without problems.
So, what’s the easiest workaround when you need avg of DATEs ?
sokrates@11.2 > select
2 sysdate - avg ( sysdate - created ) "avg(created)"
3 from dba_users;
avg(created)
--------------------
15-oct-2010 21:19:00
is neat, isn’t it ?
And: it’s deterministic (doesn’t depend on SYSDATE) !
(proof of that is left to the reader)
