Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
December 18, 2006, 11:40 pm
rate this thread
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
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.
Fields: RecordTime (format is 20060101 120000), OutTemp
If I were to take another stab at it pseudo-code like it I'd do
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.
Re: SELECT multiple rows with average of a range of values
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 ) ;
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.
- » help to retieve only the difference in minutes between two dates in DATETIME format
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum