Re: newby question finding average

"Dave" <dave_at_powerup.com.au> a écrit dans le message de news: lbt7r1d7gvfh5b334ss7vs2gg5mp4jkki0_at_4ax.com...
| 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

You can't in this way but you can do something like:

select distinct extract (year from the_date) year,

    avg(temperature) over(partition by extract (year from the_date)) avgYear,    avg(temperature) over() AvgGlobal
from mytable
where locatios in (a, b, c)
/

Regards
Michel Cadot Received on Thu Dec 29 2005 - 12:36:06 CST