List:General Discussion« Previous MessageNext Message »
From:Ashley M. Kirchner Date:January 6 2007 10:54am
Subject:Selecting avg()...
View as plain text  
    This is a continuation of something I started with a few weeks ago.  
So, here's the previous information again:


table: data
+------------+-----------------------+------+-----+-------------------+----------------+ 

| Field      | Type                  | Null | Key | Default           | 
Extra          |
+------------+-----------------------+------+-----+-------------------+----------------+ 

| uid        | mediumint(8) unsigned | NO   | PRI | NULL              | 
auto_increment |
| temp_f     | float(4,1)            | YES  |     | NULL              
|                |
| temp_c     | float(4,1)            | YES  |     | NULL              
|                |
| windchill  | float(4,1)            | YES  |     | NULL              
|                |
| dewpoint   | float(4,1)            | YES  |     | NULL              
|                |
| time       | timestamp             | NO   |     | CURRENT_TIMESTAMP 
|                |
+------------+-----------------------+------+-----+-------------------+----------------+ 



    This table gets information every minute.  And for a '24 hour' 
graph, I perform the following select:

    select day(time) as the_day,
           hour(time) as the_hour,
           avg(temp_f) as avg_temp_f,
           avg(temp_c) as avg_temp_f,
           avg(windchill) as avg_windchill,
           avg(dewpoint) as avg_dewpoint
           from data
           where time > now() - interval 24 hour
           group by the_day, the_hour;

    And this returns the data I need.  However, I'd like a finer grain 
select, if possible.  For example, instead of it averaging each 60 
minute period, I'd like an average per 15 minute.  So that I get 4 data 
points returned per hour, instead of just one.

    Possible?

    -- A

Thread
Selecting avg()...Ashley M. Kirchner6 Jan
  • Re: Selecting avg()...Dan Nelson6 Jan