Re: Complex SQL

Simply create a function

SQL> desc junk

 Name                         Type
 -------------------------------------
 WHEN                                               DATE
 VAL                                                NUMBER

---
CREATE OR REPLACE FUNCTION running_avg ( when_date in date)
RETURN NUMBER
IS
 MANY NUMBER;
BEGIN
SELECT avg(val)
into many
FROM junk
WHERE when < = when_date;
return many;
EXCEPTION WHEN OTHERS THEN RETURN(0);
END;

---
write your select statement

 select when,running_avg(when)
 from junk2


"Jim" <jschneider_at_kjinteractive.com> wrote in message
news:8e6a91e3.0210250837.57983b3b_at_posting.google.com...


> Maybe not so complex, but I don't know how to do it.


>

> My table is setup like this:

>

> Date             Numeric Value

> --------------   --------------

> 01/02/2002       10

> 01/03/2002       17

> 01/04/2002       83

> ....

>

> What I'd like to do is to get the average (AVG) of

> the "Numeric Value" column at each date interval, with a

> single SQL statement. That is, an SQL statement that

> returns:

>

> 01/02/2002   10

> 01/03/2002   13.5  [(10 + 17)/2]

> 01/04/2002   55    [(10 + 17 + 83)/3]

>

> Thanks in advance for any help.