Professional Web Applications Themes

query assistance - MySQL

I have a query question. I have a table of hourly weather observations and I am trying to determine normal high temperatures for each date (date being month/day). This requires finding the max temp for a day for thirty years and averaging those values. I have the query below which gives me the 30 max temp values for 11/30 from the years 1961 - 1990 but I need the average of those values. In fact I would like to remove the %-11-30 requirement and give me the answer for all days in a year. SELECT date(datetime),max( temp ) FROM `KPIT` ...

  1. #1

    Default query assistance

    I have a query question. I have a table of hourly weather observations
    and I am trying to determine normal high temperatures for each date
    (date being month/day). This requires finding the max temp for a day
    for thirty years and averaging those values. I have the query below
    which gives me the 30 max temp values for 11/30 from the years 1961 -
    1990 but I need the average of those values. In fact I would like to
    remove the %-11-30 requirement and give me the answer for all days in a
    year.

    SELECT date(datetime),max( temp )
    FROM `KPIT`
    WHERE year( datetime )
    BETWEEN 1961
    AND 1990 and date(datetime) like '%-11-30'
    GROUP BY date( datetime )

    Any help would be greatly appreciated.

    Bahrmann Guest

  2. #2

    Default Re: query assistance

    Bahrmann wrote: 
    Have a look at the avg function. Syntax is avg(column-name).
    Depending on the version of MySQL you are using you may be able
    to use your existing query as a sub query and apply avg to the
    result.

    If you are using an older version of MySQL you could use your
    existing query to build a temporary table and then do an avg on
    the temperature column.

    HTH

    Jerry
    Jerry Guest

  3. #3

    Default Re: query assistance

    Thanks for the followup. About 15 minutes after I posted this I
    figured it out using a sub query as you suggested. For informational
    purposes the query looks like:
    SELECT count(max_temp) as year_count, avg(max_temp) as normal_high
    FROM (
    SELECT max(temp) AS max_temp
    FROM `KPIT` where year(datetime) between 1971 and 2000 and
    date(datetime) like '%-11-30' GROUP BY date(datetime)
    ) as KPIT

    Jerry Gitomer wrote: 
    > Have a look at the avg function. Syntax is avg(column-name).
    > Depending on the version of MySQL you are using you may be able
    > to use your existing query as a sub query and apply avg to the
    > result.
    >
    > If you are using an older version of MySQL you could use your
    > existing query to build a temporary table and then do an avg on
    > the temperature column.
    >
    > HTH
    >
    > Jerry[/ref]

    Bahrmann Guest

Similar Threads

  1. Need Assistance PLEASE
    By Debi Cass in forum Macromedia Contribute Connection Administrtion
    Replies: 2
    Last Post: December 28th, 08:19 PM
  2. Assistance with Query Optimization
    By GS in forum MySQL
    Replies: 4
    Last Post: September 13th, 09:27 PM
  3. I could use some assistance...
    By atech in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: September 20th, 04:34 PM
  4. Need Urgent assistance with a SQL Query
    By Sandeep Commar in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: July 23rd, 07:01 AM
  5. SQL Query Assistance
    By Sam Commar in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 10th, 12:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139