Re: newby question finding average
- From: Dave <dave@xxxxxxxxxxxxxx>
- Date: Fri, 30 Dec 2005 16:00:56 +1000
On Thu, 29 Dec 2005 19:36:06 +0100, "Michel Cadot"
<micadot{at}altern{dot}org> wrote:
>
>"Dave" <dave@xxxxxxxxxxxxxx> a écrit dans le message de news: lbt7r1d7gvfh5b334ss7vs2gg5mp4jkki0@xxxxxxxxxx
>| On Thu, 29 Dec 2005 14:59:17 +0100, "Michel Cadot"
>| <micadot{at}altern{dot}org> wrote:
>|
>| >
>| >"Dave" <dave@xxxxxxxxxxxxxx> a écrit dans le message de news: oln7r1ljrm3t8ph4a6jdbt07873ps5a82u@xxxxxxxxxx
>| >| Am using sqlplus
>| >|
>| >| On Thu, 29 Dec 2005 22:50:52 +1000, Dave <dave@xxxxxxxxxxxxxx> 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
>
I'm not quite sure what you mean but will work on it for a while with
one of my more knowledgeable colleagues and I appeciate you're advice
Michel
Bye for now, Dave
.
- References:
- newby question finding average
- From: Dave
- Re: newby question finding average
- From: Dave
- Re: newby question finding average
- From: Dave
- Re: newby question finding average
- From: Michel Cadot
- Re: newby question finding average
- From: Dave
- Re: newby question finding average
- From: Michel Cadot
- newby question finding average
- Prev by Date: Re: trivia: origin of names for UPPER() and LOWER()
- Next by Date: How should we run XP from Linux.
- Previous by thread: Re: newby question finding average
- Next by thread: Q: how to modify the storage params of a partition
- Index(es):
Relevant Pages
|
|
