Re: Compute average SQL statement

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 7 Jan 2004 17:25:11 GMT
Message-ID: <bthfdm$780hi$1@ID-82536.news.uni-berlin.de>

> Fine. That's it.
> Can i group the avg by year? The week column is in date format.

Sure,

analytic functions can make use of the 'partition by <expr>' in the statement:

  drop table avg_;

  create table avg_ (
    week date,
    value_a number
  );   

  insert into avg_ values (sysdate-7*22, 300);
  insert into avg_ values (sysdate-7*21, 500);
  insert into avg_ values (sysdate-7*20, 600);
  
  insert into avg_ values (sysdate,       10);

  insert into avg_ values (sysdate+ 7*1, 20);   insert into avg_ values (sysdate+ 7*2, 5);   

  select avg(value_a) over (
    partition by trunc(week, 'YEAR')
    order by week
    range unbounded preceding)
  from
    avg_;   

hth

Rene Nyffenegger

> 
> "Rene Nyffenegger" <rene.nyffenegger_at_gmx.ch> schrieb im Newsbeitrag
> news:btgong$6rcte$1_at_ID-82536.news.uni-berlin.de...

>>
>> > Hi,
>> >
>> > i'm looking for a sql statement to compute an average from the following
>> > sample:
>> >
>> > Week Value_a Avg.
>> > -----------------------------------------------------------
>> > 1 10 10 / 1= 10
>> > 2 20 (10+20) / 2 = 15
>> > 3 5 (10+20+5 ) / 3 = 11,66
>> > ....
>> >
>> >
>> > The average Avg. should be computed out of Value_a for each week.
>> >
>> > Thanks.
>> >
>> > Hendrik
>>
>>
>> Hendrik,
>>
>> Try analytical functions:
>>
>>
>> create table avg_ (
>> week number,
>> value_a number
>> );
>>
>> insert into avg_ values (1, 10);
>> insert into avg_ values (2, 20);
>> insert into avg_ values (3, 5);
>>
>>
>> select avg(value_a) over (order by week range unbounded preceding) from

> avg_;

>>
>>
>> hth
>> Rene
>>
>>
>>
>> --
>> Rene Nyffenegger
>> http://www.adp-gmbh.ch

>
>

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Wed Jan 07 2004 - 11:25:11 CST