Home » SQL & PL/SQL » SQL & PL/SQL » Need an average by year of an average by month (Oracle/SQL)
Need an average by year of an average by month [message #300478] Fri, 15 February 2008 10:22 Go to next message
sqlprdgyNtrng
Messages: 2
Registered: February 2008
Junior Member
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60
Re: Need an average by year of an average by month [message #300480 is a reply to message #300478] Fri, 15 February 2008 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Post a test case: create table and insert statements.
Also post the result you want with these data (align the columns).

Regards
Michel
Re: Need an average by year of an average by month [message #300499 is a reply to message #300478] Fri, 15 February 2008 12:34 Go to previous messageGo to next message
sqlprdgyNtrng
Messages: 2
Registered: February 2008
Junior Member
My apologies.

Found answer already.

Thanks,
Brandon
Re: Need an average by year of an average by month [message #300534 is a reply to message #300499] Fri, 15 February 2008 15:22 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It would be nice if you could share your solution with us. Ofcourse as @Michael mentioned use the code tags around the sql and data.

Regards

Raj
Previous Topic: select ceratin records with the same 'chain_id' when one record has a 'completion_code' = 'RPC'
Next Topic: Using DBMS_REDEFINITION.START_REDEF_TABLE I get SP2-0027: Input is too long
Goto Forum:
  


Current Time: Thu Dec 08 00:35:38 CST 2016

Total time taken to generate the page: 0.11987 seconds