By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

SELECT multiple rows with average of a range of values

P: n/a
Hi All,

I've been racking my brain trying to figure out some sort of Sub-Select
mySQL statement that will create a result with multiple rows of
averaged values over a years time.

What I have is weather data. There is a new record every 5 minutes,
every day. So. What I want to do with one SQL statement is figure out
the Average of those 5 minute records over each day, for every day of
the year.

I've tried what seems now like a thousand different queries but i'm not
grasping something, I'm falling down when I try to figure out how to
continue selecting the range of records each day. I humbly ask if
someone here could make sense of it.

My dataset is very simple.

Table: archive
Fields: RecordTime (format is 20060101 120000), OutTemp

If I were to take another stab at it pseudo-code like it I'd do
something like

SELECT * FROM archive Where RecordTime is 2006 AND (SELECT AVG(OutTemp)
WHERE RecordTime is between 00:00 and 24:00)

It's figuring out how to replace the hours with something that COUNTS
(?) every instance of that range?

I'm confusing myself more just typing this message.

I should go lie down or something :)

Thank you any and all for your help.

Chris

Dec 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

ch******@gmail.com wrote:
Hi All,

I've been racking my brain trying to figure out some sort of Sub-Select
mySQL statement that will create a result with multiple rows of
averaged values over a years time.

What I have is weather data. There is a new record every 5 minutes,
every day. So. What I want to do with one SQL statement is figure out
the Average of those 5 minute records over each day, for every day of
the year.

I've tried what seems now like a thousand different queries but i'm not
grasping something, I'm falling down when I try to figure out how to
continue selecting the range of records each day. I humbly ask if
someone here could make sense of it.

My dataset is very simple.

Table: archive
Fields: RecordTime (format is 20060101 120000), OutTemp

If I were to take another stab at it pseudo-code like it I'd do
something like

SELECT * FROM archive Where RecordTime is 2006 AND (SELECT AVG(OutTemp)
WHERE RecordTime is between 00:00 and 24:00)

It's figuring out how to replace the hours with something that COUNTS
(?) every instance of that range?

I'm confusing myself more just typing this message.

I should go lie down or something :)

Thank you any and all for your help.

Chris
Wouldn't you want something like:

SELECT DATE( RECORDTIME )
DAY , AVG( outtemp )
FROM archive
GROUP BY DATE( RECORDTIME )

?

Dec 19 '06 #2

P: n/a
Thank you very much strawberry!

I was looking at the DATE function after I sent my original message and
your suggestion has really simplified things.

For posterity... here's the final SQL statement that I came up with
last night to grab all of the weather data from the past Year, AVG it
by day, and spit it out with the date formated as "Jan 12" so that I
can use it for graphing.

SELECT ROUND((AVG(OutTemp)),2), DATE_FORMAT(DATE(RecordTime),'%b
%d')DAY FROM archive WHERE RecordTime >= DATE_SUB(CURRENT_DATE,
INTERVAL 1 YEAR) GROUP BY DATE(RECORDTIME ) ;

http://www.alberniweather.ca/Yearly.php

If anyone has any suggestions on optimizations, it'd be greatly
appreciated, but my feeling is that this is about as good as it gets,
it is certainly querying very nice and fast.

Thanks

Chris
strawberry wrote:
ch******@gmail.com wrote:
Hi All,

I've been racking my brain trying to figure out some sort of Sub-Select
mySQL statement that will create a result with multiple rows of
averaged values over a years time.

What I have is weather data. There is a new record every 5 minutes,
every day. So. What I want to do with one SQL statement is figure out
the Average of those 5 minute records over each day, for every day of
the year.

I've tried what seems now like a thousand different queries but i'm not
grasping something, I'm falling down when I try to figure out how to
continue selecting the range of records each day. I humbly ask if
someone here could make sense of it.

My dataset is very simple.

Table: archive
Fields: RecordTime (format is 20060101 120000), OutTemp

If I were to take another stab at it pseudo-code like it I'd do
something like

SELECT * FROM archive Where RecordTime is 2006 AND (SELECT AVG(OutTemp)
WHERE RecordTime is between 00:00 and 24:00)

It's figuring out how to replace the hours with something that COUNTS
(?) every instance of that range?

I'm confusing myself more just typing this message.

I should go lie down or something :)

Thank you any and all for your help.

Chris

Wouldn't you want something like:

SELECT DATE( RECORDTIME )
DAY , AVG( outtemp )
FROM archive
GROUP BY DATE( RECORDTIME )

?
Dec 20 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.