Query question

From: Gary Meyers <dbsgrm_at_arco.com>
Date: Thu, 21 Apr 1994 18:11:29 GMT
Message-ID: <1994Apr21.181129.15056_at_Arco.COM>


Another in the series of "Gee, I know I'm missing something really dumb, but??"

I have 2 tables

Station                               Temp
-----------                           -----------------
Station_id                            Station_id
Lat                                   Temp      (temperature)
Lon                                   Month
Elev                                  Yr

My user wants the mean and standard deviation of Temps for each station for the period between 1975 and 1987 (1976 thru 1986 inclusive).

 I'm constructing a temp table TEMP_RESULTS, with columns

Station_id Lat Lon Elev Mean Stdev

(I plan on having my script create the table temporarily, then drop it after the
info is spooled out to a file for my user's use)

I populate the temp results table with:

insert into temp_results
(station_id,lat,lon,elev)

select station_id,lat,lon,elev
from station
;

I want to update the temp_results table with the mean and standard dev of the temps. I have:

update temp_results tr
set (mean,stdev) =
(select avg(temp),stddev(temp)

from station s,temp t
 where s.station_id = t.station_id
and s.station_id in (select station_id from station) and yr between '1976' and '1987'
group by s.station_id)
where tr.station_id in (select station_id from station)

I know the select avg(temp),stddev(temp) part works, but I am, of course, getting a

"ORA-01427: single-row subquery returns more than one row" error.

Thanks for the help, folks.

--
Gary Meyers                     : Usual disclaimer here
gary.meyers_at_Edm.Arco.COM        : My opinions, etc. etc. 
Received on Thu Apr 21 1994 - 20:11:29 CEST

Original text of this message