SQLTeam.com | Weblogs | Forums

Monthly averages for multiples months for several years


#1

I am having a data set in the format which i'm attaching below. It contains data for, say, 25 years on a daily basis. I have to take out averages of each column (AA, BB, CC,DD) omitting null values, for a single / multiple months (not all months together) year wise: like avg of AA for the month of Jan and Jul from 90-95. I'm no table to frame a proper query.

NAME DD MM YYYY TIME AA BB CC DD

ABC 01 01 1986 0100 0
ABC 01 01 1986 0200 0
ABC 01 01 1986 0230 0 6 5 94
ABC 01 01 1986 0300 0
ABC 01 01 1986 0400 0
ABC 01 01 1986 0500 0
ABC 01 01 1986 0530 0 6 5 94
ABC 01 01 1986 0600 0 6
ABC 01 01 1986 0700 0 6
ABC 01 01 1986 0800 0 8
ABC 01 01 1986 0830 0 9 8 95
ABC 01 01 1986 0900 0 9
ABC 01 01 1986 1000 2 14
ABC 01 01 1986 1100 2 17
ABC 01 01 1986 1115 5
ABC 01 01 1986 1130 7 17 9 60
ABC 01 01 1986 1140 7
ABC 01 01 1986 1145 7
ABC 01 01 1986 1150 7
ABC 01 01 1986 1200 8 18
ABC 01 01 1986 1300 6 18
ABC 01 01 1986 1400 10 18
ABC 01 01 1986 1430 7 18 8 50


#2

You can use the AVG function like this:

SELECT
	YYYY,
	MM,
	AVG(AA) AS AvgAA,
	AVG(BB) AS AvgBB
FROM
	YourTable
WHERE
	YYYY >= 1990
	AND YYY <= 1995
GROUP BY
	YYYY,
	MM;