Re: Complex SQL

Ummm,

Steve, I don' think your sql will give Jim the answer he seeks. Your statement gives the averages for each date, but Jim wants the cumulative average of all past dates up to and including the reported date.

Jim, I can't think of a pure sql approach either, certainly not in one statement. Is there only one value per date? If so, I'd be tempted to add a column to the table to hold the running average, and add a pre-insert trigger to compute the value at the time the new row is inserted. Even then, I think you'd need a temp table to avoid the mutating table error.

If you're working with existing data, I fear a pl/sql function might be needed.

hth

rww

On Fri, 25 Oct 2002 12:43:23 -0700, "Steve M" <steve.mcdaniels_at_vuinteractive.com> wrote:

>select trunc(the_date) the_date,
> avg(the_numeric_value) the_average_value
>from the_table
>group by trunc(the_date)
>
>I'm sure that you are aware that this is covered in the Oracle
>documentation,
>but like many of us, you are too lazy to look it up.
>
>"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.
>

Russ Wolfe
wolfer_at_peak.org Received on Fri Oct 25 2002 - 22:49:43 CEST