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 wrote:
> 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.
>
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

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:
> Bahrmann wrote:
> > 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.
> >
> 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