Re: sql-proplem

Ansgar Seiter wrote:
>
> 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 :
> 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
> Germany
>
> If you want to e-Mail me, please append the above address to
> ansgar.seiter + masterspace

Try:

SELECT a.group_id, a.weather_id,
AVG(NVL(a.value,0))
FROM value_table a, weather b
WHERE a.weather_id (+) = b.weather_id
GROUP BY group_id, a.weather_id

Bob Morrison Received on Fri May 30 1997 - 00:00:00 CEST