Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

23 novembre 2005 3 23 /11 /novembre /2005 16:31

I have seen an interresting question on a forum about how to calculate average on DATE columns.

It is not possible to calculate average on non-numeric values:


SQL> SELECT AVG( creation ) FROM paragraphes
  2  /
SELECT AVG( creation ) FROM paragraphes
            *
ERREUR à la ligne 1 :
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

SQL>

So you have to convert the date to a numeric value:

SQL> SELECT TO_CHAR( creation ) FROM PARAGRAPHES WHERE ROWNUM <= 5
  2  /

TO_CHAR(CR
----------
18/02/2004
25/02/2004
04/03/2004
04/03/2004
17/03/2004

SQL> SELECT TO_DATE(TRUNC(AVG( TO_CHAR(creation,'J'))),'J') FROM PARAGRAPHES
  2  WHERE ROWNUM <= 5
  3  /

TO_DATE(TR
----------
02/03/2004

SQL>

Francois

Partager cet article

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article

commentaires