Re: Query question

In article <1994Apr21.181129.15056_at_Arco.COM>, dbsgrm_at_arco.com (Gary Meyers) writes:
|> 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.
|>
|>
Don't use the temp table, unless performance is an issue.

Try this for an annual reading:
select a.station_id, lat, lon, elev, yr, avg(temp), stddev(temp) from station a, temp b
where a.station_id=b.station_id
group by a.station_id, yr;

To exactly mimic your request, use:
select a.station_id, lat, lon, elev, avg(temp), stddev(temp) from station a, temp b
where a.station_id=b.station_id
and yr between 1976 and 1987
group by a.station_id;

Then you won't need a temporary table.

You could make this a view, if it's run often...

Vary the group_by clause to get other time periods. Received on Fri Apr 22 1994 - 00:07:35 CEST