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

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. Received on Wed Mar 17 2004 - 01:40:52 CST