Re: newby question finding average

On Thu, 29 Dec 2005 14:59:17 +0100, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
>"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
>

thanks Michel
once I have computed avg of temperature on report how do I assign an alias to this value.. do I just add ..

col "Ave" new_value avger
compute avg of temperature on report "Ave"

and use &&aver as required

Dave Received on Thu Dec 29 2005 - 08:46:59 CST