calculating 30 year daily average

[Guest]

Hi everyone,

I have a dataset of daily precip values over a 30 year time span (a table
with 11,000 plus records) . So basically for everyday (date) of the year I
have 30 precip values. I am trying to create a table that contains the
average precip for every date, i.e. Jan 1, Jan 2, etc., over those 30 years,
so then my resulting table will have 365 records. I am able to label each
date in a format such that Jan 1 is "0101", Jan 2 is "0102" and so on, but am
stuck now on how to use the Avg function to average all "0101" dates and so
on.

Any help is appreciated, thanks.

 

[MGFoster]

Hi everyone,

I have a dataset of daily precip values over a 30 year time span (a table
with 11,000 plus records) . So basically for everyday (date) of the year I
have 30 precip values. I am trying to create a table that contains the
average precip for every date, i.e. Jan 1, Jan 2, etc., over those 30 years,
so then my resulting table will have 365 records. I am able to label each
date in a format such that Jan 1 is "0101", Jan 2 is "0102" and so on, but am
stuck now on how to use the Avg function to average all "0101" dates and so
on.

Not sure what you're getting at. Try this:

SELECT Month(date_column) As Mon,
Day(date_column) As Dy,
Avg(precip_value) As PrecipAvg

FROM table_name

 

[John Spencer (MVP)]

SELECT Format(YourDate,"mmdd"), Avg(Precipitation)
FROM YourTable
GROUP BY Format(YourDate,"mmdd")