Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> newby question finding average
I would like to find the average temperature combining a number of
locations where the record overlaps.
for a particular month I could use ...
sum(round(avg(decode(to_char(to_date,'mm'), '01',temp, null)), 1))
"Jan" from table where locatios in (a, b, c)
If I had the following data..
location 1 location 2 location 3 Jan 1999 25.1 26 27 Jan 2000 25.1 20 Jan 2001 25.3 Jan 2002 25.3
the above script would result in 24.8 because it averages all the figures avalable
however I would like to average the temperature across all available locations for each year first and then add the averaged figures
Jan 1999 = 26 Jan 2000 = 22.5 Jan 2001 = 25.3 Jan 2002 = 25.3
results in 24.8 and this the figure I mant.
Is there a way to decode by the year, find the average of each year and then average the resulting figures.
I want only the one figure for each month as an average across all years of record using many locations with varying overlaps.
This would seem to be a simple task but has me stumped!
Dave Received on Thu Dec 29 2005 - 06:18:14 CST