sql-proplem

[Quoted] Hi there,

I have a problem with an sql-task I couldn't surmount om my own. So mebbie some of you could help me there :

I have 2 tables :
[Quoted] TABLE value_table

      group_id   number
      weather_id number
      value      number

TABLE weather
      weather_id number
       :           :
      (rest not important)

Now I want to compute the average of value of table value_table grouped by group_id and weather_id. And I want this for every existing weather_id in weather. The Problem is, that not every possible weather_id will be referenced in value_table and I want a zero to be returned as avg(value) for the missing weather_ids for EVERY group_id in value_table.
Using a select statement like :
SELECT a.group_id, a.weather_id,
DECODE(AVG(a.value),NULL,0,AVG(a.value)) FROM value_table a, weather b
WHERE a.weather_id (+) = b.weather_id
GROUP BY group_id, a.weather_id

doesn't do it, because it returns only one row per missing weather_id and not as many as different group_ids there are, of course. If still unclear I can give you a small example :

value_table :

group_id weather_id value

    1		    10		 5
    2		    10		 6
    1		    20		 8
    2		    20		 9

weather :

weather_id

  10
  20
  30

The output from the above statement would be (I hope, I put teh statement down correctly, 'cause I had to write it out of my memory)

group_id weather_id value

    1		    10		 5
    2		    10		 6
    1		    20		 8
    2		    20		 9
                    30           0

what I want is :

    1		    10		 5
    2		    10		 6
    1		    20		 8
    2		    20		 9
    1               30           0
    2               30           0

How do I achieve that ? I hope it's not too trivial ...

thanx in advance

Ansgar
-

 Ansgar Seiter          	 Group Prof. Ch. Schlier      
 Department of Physics  	                             
 University of Freiburg 	 Phone:  +49 761 203-5730      
 Hermann-Herder-Str. 3  	 FAX:    +49 761 203-5873            
 D-79104 Freiburg i.Br  	 E-Mail: physik.uni-freiburg.de   

If you want to e-Mail me, please append the above address to ansgar.seiter + masterspace Received on Fri May 30 1997 - 00:00:00 CEST