Re: Urgent: SQL PROBLEM--AVG value of current period and accumulated period at the same time ?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 20 Mar 2004 17:26:44 +0100
Message-ID: <405c7087$0$277$626a14ce@news.free.fr>

"Coca" <bigmelon_at_btamail.net.cn> a écrit dans le message de news:c38vhu$24kpdc$1_at_ID-189205.news.uni-berlin.de...
> Hi,
>
> I am working on a project that needs both an average value of the current
> period and
> an average value of an accumulated period which I encouted a problem.
>
> Table name: indicator
>
> organ_name char(12)---- organization name
> sDate date
> indicValue number(18,4)
>
> Records of the table :
>
> organ_name sDate indicValue
> ==========================================
>
> organ1 2004-1-30 100
> organ1 2004-1-31 110
> organ1 2004-2-1 120
> organ1 2004-2-2 130
>
> organ2 2004-1-30 200
> organ2 2004-1-31 210
> organ2 2004-2-1 200
> organ2 2004-2-2 210
> organ2 2004-2-3 220
> organ2 2004-2-4 230
>
> organ3 2004-2-2 310
> organ3 2004-2-3 320
>
> If users choose a period : from 2004-2-1 to 2004-2-4,
> currAvg: the average value of the current period=avg(from 2004-2-3 to
> 2004-2-4) group by organ_name.
> AccuAvg: the average value of the accumulated period=avg(from 2004-2-1 to
> 2004-2-4) group by organ_name.
>
> so the result should be :
>
> organ_name currAvg AccuAvg
> =======================================================
> organ1 null (120+130)/2
> organ2 (220+230)/2 (200+210+220+230)/4
> organ3 320/1 (310+320)/2
>
>
> I tried this:
>
> SELECT a1.organ_name,AVG(a1.indicValue) as "CurrAVG", AVG(a2.indicValue) as
> "AccuAvg"
> FROM indicator a1,indicator a2
> WHERE a1.organ_name=a2.organ_name
> AND a1.sDate between
> to_date('2004-2-3','yyyy-mm-dd') and to_date('2004-2-4','yyyy-mm-dd')
> AND a1.sDate between
> to_date('2004-2-1','yyyy-mm-dd') and to_date('2004-2-4','yyyy-mm-dd')
>
>
> The result does not have records related to "organ1", since "organ1" does
> not have records in
> 2004-2-3 and 2004-2-4, so I am trying to find another method.
>
>
> Could someone kindly help me?
>
> Any tips are appreciated.
> Thank you very much.
>
>

Just use an outer join:

SQL> l
  1 select a2.organ_name, avg(a1.indicValue) "CurrAvg", avg(a2.indicValue) "AccuAvg"   2 from indicator a1, indicator a2
  3 where a1.organ_name (+) = a2.organ_name   4 and a1.sDate (+) between to_date('2004-2-3','YYYY-MM-DD')

  5                         and to_date('2004-2-4','YYYY-MM-DD')
  6    and a2.sDate between to_date('2004-2-1','YYYY-MM-DD')
  7                     and to_date('2004-2-4','YYYY-MM-DD')

  8* group by a2.organ_name
SQL> / ORGAN_NAME CurrAvg AccuAvg
------------ ---------- ----------

organ1                         125
organ2              225        215
organ3              320        315

Regards
Michel Cadot Received on Sat Mar 20 2004 - 10:26:44 CST