SELECT multiple rows with average of a range of values

[chrisale]

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

[strawberry]

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 )

?

[chrisale]

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 )

?