Need an average by year of an average by month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need an average by year of an average by month

Author

Topic

sqlprdgyNtrng
Starting Member

2 Posts

Posted - 2008-02-15 : 10:49:06

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-15 : 10:54:14

Seems to be an ORACLE question. Please post on forums like www.orafaq.com or www.dbforums.com. This site is exclusively for SQL Server related questions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sqlprdgyNtrng
Starting Member

2 Posts

Posted - 2008-02-15 : 11:07:22

ah hah! ...explains why I haven't found an answer yet...

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-15 : 12:18:19

You should realize that taking an average of an average is a completely invalid concept.

What you need to do is take the average of the original data, not averages of averages.

To illustrate:
Compute the average wait time for a call center to answer calls on day 1, day 2, and for both days.
On day 1, they take 100,000 calls with a total wait time of 500,000 seconds for an average wait time of 5 seconds.
On day 2, they take 5,000 calls with a total wait time of 100,000 seconds for an average wait time of 20 seconds.
The average of the average would be (20+5)/2 = 12.5
The actual average is (500,000+100,000)/105,000 = 5.71 seconds

CODO ERGO SUM

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-18 : 06:21:45

"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. "

This logic is wrong for most of the cases.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources