René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

avg aggregate function [Oracle SQL]

select
  avg( numeric-expression ) [, col_1]
from table_name
[group by col_1 ]
sum is an aggregate function. It returns the average of its numeric-expressions for each group indicated in the group by clause.
If the expression in avg() evaluates to null, it is disregarded: 
create table avg_null (
  txt varchar2(10),
  num number
);

insert into avg_null values ('***',   1);
insert into avg_null values ('***',   2);
insert into avg_null values ('***',   3);
insert into avg_null values ('***',null);

insert into avg_null values ('%%%',  17);
insert into avg_null values ('%%%',  20);
insert into avg_null values ('%%%',  23);
insert into avg_null values ('%%%',null);
insert into avg_null values ('%%%',null);

select avg(num)              avg_num_1, 
       sum(num) / count(*)   avg_num_2,
       sum(num) / count(num) avg_num_3,
       txt                   txt
  from avg_null 
 group by txt;

 AVG_NUM_1  AVG_NUM_2  AVG_NUM_3 TXT
---------- ---------- ---------- ----------
         2        1.5          2 ***
        20 14.2857143         20 %%%