Re: SQL*PLUS question

On Wed, 17 Mar 1999 16:22:58 GMT, jeff109_at_NOSPAM.netscape.net wrote:

>On Tue, 16 Mar 1999 22:37:20 -0500, "Hemei" <Hemei_at_yahoo.com> wrote:
>
>>I have one table, table_a which has two attributes group_id and rate. What I
>>want is to retrieve the medium value for each group_id. For example,
>> table_a:
>> group_id rate
>> ------------ ------
>> 01 1.0
>> 01 1.1
>> 01 1.2
>> 02 2.1
>> 03 3.1
>> 03 3.4
>> 03 4.0
>> 03 4.1
>> 03 5.0
>>what I want is something like:
>> group_id rate
>> ------------ ------
>> 01 1.1
>> 02 2.1
>> 03 4.0
>>Is there a easy way to do this with sql*plus? Thanks.
>>
>>Peng Nie
>>
>>
>
>Well, I doubt you'd be asking this question if you were after just the
>average. I believe you are seeking the median value. There is no
>really easy way that I know of in Oracle to get this value. Oracle is
>not very robust in the statistical calculation area, from what I've
>seen. The only way I can think of doing this is sort-of complex, but
>I think it should work. If anyone can improve upon this or has a
>better way altogether, I too would be interested to see it.
>
>I am not checking syntax here - just giving a general idea of what I
>would try:
>
>select group_id, get_median(group_id) from table_a order by group_id;
>
>You would need to create this get_median function something like this:
>
>create function get_median ( p_groupid table_a.group_id%TYPE)
> return number is
>
> working_val number;
> target number;
> loop_counter number := 1;
> average_targets boolean := FALSE;
> cursor rates is select rate from table_a
> where group_id = p_groupid
> order by rate;
>begin
> select ((count(group_id) + 1)/2) into target from table_a
> where group_id = p_groupid;
>
> if target != floor(target) then
> target := floor(target);
> average_targets := TRUE;
> end if;
>
> open rates;
> loop
> if loop_counter = target then
> working_val := rates.rate;
> end if;
> if loop_counter = target + 1 then
> working_val := (working_val + rates.rate)/2;
> end if;
>
> if average_targets = TRUE
> exit when loop_counter = target + 1;
> else
> exit when loop_counter = target;
> end if;
> loop_counter := loop_counter + 1;
> end loop;
> return working_val;
>end;
>
>Hope this works/helps.
>
>-Jeff Guttadauro
>

Ooops - forgot the fetch on the rates cursor inside the loop - should be first statement in loop... Received on Wed Mar 17 1999 - 13:33:39 CST