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

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

Am using sqlplus

On Thu, 29 Dec 2005 22:50:52 +1000, Dave <dave@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

"Dave" <dave@powerup.com.au> a écrit dans le message de news: oln7r1ljrm3t8ph4a6jdbt07873ps5a82u@4ax.com...
| Am using sqlplus
|
| On Thu, 29 Dec 2005 22:50:52 +1000, Dave <dave@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

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

>
>"Dave" <dave@powerup.com.au> a écrit dans le message de news: oln7r1ljrm3t8ph4a6jdbt07873ps5a82u@4ax.com...
>| Am using sqlplus
>|
>| On Thu, 29 Dec 2005 22:50:52 +1000, Dave <dave@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

"Dave" <dave@powerup.com.au> a écrit dans le message de news: lbt7r1d7gvfh5b334ss7vs2gg5mp4jkki0@4ax.com...
| On Thu, 29 Dec 2005 14:59:17 +0100, "Michel Cadot"
| <micadot{at}altern{dot}org> wrote:
|
| >
| >"Dave" <dave@powerup.com.au> a écrit dans le message de news: oln7r1ljrm3t8ph4a6jdbt07873ps5a82u@4ax.com...
| >| Am using sqlplus
| >|
| >| On Thu, 29 Dec 2005 22:50:52 +1000, Dave <dave@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

On Thu, 29 Dec 2005 19:36:06 +0100, "Michel Cadot"
<micadot{at}altern{dot}org> wrote:

>
>"Dave" <dave@powerup.com.au> a écrit dans le message de news: lbt7r1d7gvfh5b334ss7vs2gg5mp4jkki0@4ax.com...
>| On Thu, 29 Dec 2005 14:59:17 +0100, "Michel Cadot"
>| <micadot{at}altern{dot}org> wrote:
>|
>| >
>| >"Dave" <dave@powerup.com.au> a écrit dans le message de news: oln7r1ljrm3t8ph4a6jdbt07873ps5a82u@4ax.com...
>| >| Am using sqlplus
>| >|
>| >| On Thu, 29 Dec 2005 22:50:52 +1000, Dave <dave@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
>
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