Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is it possible to have a max function in SQL that exlcudes outliners(values 20x average)

Re: Is it possible to have a max function in SQL that exlcudes outliners(values 20x average)

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/07/19
Message-ID: <8l4d1a$dn9$1@s2.feed.news.oleane.net>#1/1

select max(value)
from (select avg(value) avg_val from my_table

      where account = 'my account'
      and timestamp between ... and ...) b,
     my_table a
where a.account = 'my account'

  and a.timestamp between ... and ...
  and a.value between b.avg_val/20 and 20*b.avg_val /

or

select a.account, max(a.value)
from (select account, avg(value) avg_val from my_table

      where timestamp between ... and ...
      group by account) b,
     my_table a
where a.timestamp between ... and ...

  and a.value between b.avg_val/20 and 20*b.avg_val   and b.account = a.account
group by a.account
/
--
Have a nice day
Michel


dsad <dsad_at_hotmail.com> a écrit dans le message : lXhd5.29262$Ra6.433828_at_zombie.newscene.com...

> we need to get max of a value for an account over a time period, but we want
> to exclude any values that are 20x greater or less than average for that
> account over the period
>
> any ideas?
Received on Wed Jul 19 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US