Re: newby question finding average


"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


.

Relevant Pages

  • Re: temperature (OUTPUT)
    ... When I run the script, I see a temperature of 37 degrees on hdisk0 ... Sensor = thermal sensor ... echo "ENVSTATUS $MM" ...
    (AIX-L)
  • Re: temperature (OUTPUT)
    ... When I run the script, I see a temperature of 37 degrees on hdisk0 ... Sensor = thermal sensor ... echo "ENVSTATUS $MM" ...
    (AIX-L)
  • Re: temperature (OUTPUT)
    ... 37 degrees is about normal human body temperature. ... When I run the script, I see a temperature of 37 degrees on hdisk0 ... you can start temp monitoring ...
    (AIX-L)
  • Re: temperature (OUTPUT)
    ... N.B. Unsolicited email from vendors will not be appreciated. ... Subject: temperature (OUTPUT) ... When I run the script, I see a temperature of 37 degrees on hdisk0 ... # script just control if there is limit violation ...
    (AIX-L)
  • Re: temperature (OUTPUT)
    ... 37 degrees is about normal human body temperature. ... When I run the script, I see a temperature of 37 degrees on hdisk0 ... you can start temp monitoring ... # script just control if there is limit violation ...
    (AIX-L)