Re: sql-proplem

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/05/30
Message-ID: <338f3031.4695190_at_www.sigov.si>#1/1

One solution would be to union two selects: the first one will retrieve matching combinations from both tables (without outer join) and the second will retrive all combinations of missing weather_ids and group_ids from value_table (by doing cartesian product) with value column in the output set to 0.

Something like this:

SQL> SELECT a.group_id, b.weather_id, AVG(a.value)   2 FROM value_table a, weather b
  3 WHERE a.weather_id = b.weather_id   4 GROUP BY group_id, b.weather_id
  5 UNION
  6 SELECT DISTINCT a.group_id, b.weather_id,0   7 FROM value_table a, weather b
  8 WHERE b.weather_id NOT IN
  9 (SELECT weather_id FROM value_table)  10 /

 GROUP_ID WEATHER_ID AVG(A.VALUE)
--------- ---------- ------------

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

6 rows selected.

Regards,

Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3

e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000

---

On Fri, 30 May 1997 13:23:48 +0200, Ansgar Seiter <seiter_at_phya5.physik.uni-freiburg.de> wrote:

>I have 2 tables :
>TABLE value_table
> group_id number
> weather_id number
> value number
>TABLE weather
> weather_id number
>
>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 :
> [SNIP]
Received on Fri May 30 1997 - 00:00:00 CEST