Re: newby question finding average

"Dave" <dave_at_powerup.com.au> a écrit dans le message de news: oln7r1ljrm3t8ph4a6jdbt07873ps5a82u_at_4ax.com...
| Am using sqlplus
|
| On Thu, 29 Dec 2005 22:50:52 +1000, Dave <dave_at_powerup.com.au> wrote:
|
| >
| >Sorry better to use a diiferent set of data :)
| >
| >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 40
| >Jan 2000 25.1 20
| >Jan 2001 25.3
| >Jan 2002 25.3
| >
| >the above script would result in 26.7 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 = 30.4
| >Jan 2000 = 22.5
| >Jan 2001 = 25.3
| >Jan 2002 = 25.3
| >
| >results in 25.9 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!
| >
| >I also wish to save the average figure as a new_value variable and use
| >elsewhere down in the script. I am fine with this part.
| >
| >thanks Dave

break on report
compute avg of temperature on report
select extract (year from the_date) year, avg(temperature) temperature from mytable
where locatios in (a, b, c)
group by extract (year from the_date)
/

Regards
Michel Cadot Received on Thu Dec 29 2005 - 07:59:17 CST