#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Question Averages and grouping issue


    Hi folks,

    I have a table filled with weatherdata, containing temperature, air pressure, wind speed etc. values. Now I'm trying to aggregate and get daily averages. I'm using the following statement:

    sql Code:
    SELECT
            DAYOFMONTH(TIMESTAMP) AS DAY,
            MONTH(TIMESTAMP) AS MONTH,
            AVG(indoor_temperature) AS in_temp,
            AVG(outdoor_temperature) AS out_temp,
            AVG(indoor_humidity) AS in_humidity,
            AVG(outdoor_humidity) AS out_humidity,
            AVG(dewpoint) AS dewpoint_temp,
            AVG(windchill) AS windchill_temp,
            AVG(pressure_relative_hpa) AS air_pressure,
            AVG(wind_speed) AS wind_mps,
            AVG(rain_1h) AS rain_1h,
            AVG(rain_24h) AS rain_24h,
            MAX(rain_total) AS rain_total
    FROM weatherdata
    GROUP BY DAYOFMONTH(TIMESTAMP)
    ORDER BY TIMESTAMP DESC
    LIMIT 0, 12;


    I suppose to get returned 12 rows with one row for each day. I am getting 12 rows as expected, but instead of getting a row for each day, e.g. for december I'm only getting the 5th and the 8th day. What is wrong in my statement?

    Any help or hints highly appreciated!

    Thanks and bests,
    Martin
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    your GROUP BY is wrong

    it should be
    Code:
    GROUP
        BY DAYOFMONTH(TIMESTAMP) 
         , MONTH(TIMESTAMP)
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    Hi r937,

    thanks a lot for your help. I had already found a similar way:

    sql Code:
    SELECT
       DATE_FORMAT(TIMESTAMP, '%m/%d') AS DAY,
       ...
    FROM smartweather
    GROUP BY DAY
    ORDER BY TIMESTAMP DESC
    LIMIT 0, 12


    But I find your solution looks more sophisticated and in a way correcter than mine. So thanks a million!

IMN logo majestic logo threadwatch logo seochat tools logo