Calculating Average and Median of set of values

[BJ]

Hi,

I have data like this
03/14/05 13:09:42.0 0 CON
03/14/05 13:10:19.0 1 CON
03/14/05 13:10:21.0 2 CON
03/16/05 00:57:42.0 3 CON
03/16/05 03:14:21.0 4 CON
03/16/05 03:14:21.5 5 CON
03/19/05 16:54:57.0 6 CON
03/19/05 16:59:42.0 7 CON
03/19/05 17:04:36.5 8 CON
03/19/05 17:06:42.0 9 CON

I have written a query to calculate the hourly total number of
events for a particular 'CON.

i.e this gives the hourly count of the events for a particular date and
CON..
And also I need to calculate Average for that hour, median value for
that hour.

Suppose say, if I do a group by and get records like

MyDate MyHour Expr1002 Count SiteName
6/15/2003 16 10 2 BOW
6/15/2003 17 1 0.2 BOW
6/16/2003 7 6 1.2 BOW
6/16/2003 8 2 0.4 BOW
6/16/2003 10 1 0.2 BOW
6/16/2003 11 2 0.4 BOW
6/16/2003 14 1 0.2 BOW
6/16/2003 17 17 3.4 BOW
6/18/2003 9 7 1.4 HUN
6/18/2003 10 13 2.6 HUN
6/18/2003 11 1 0.2 BOW
6/18/2003 12 2 0.4 BOW

I get the hourly values. But I dont know how to calculate median and
average values on the set of records.

Like I want the Average value and median value for say 6/16/2003, then
for 6/18/2003 .....


I am really in need. Please help.

Thanks
BJ

 

[Tom Ellison]

Dear BJ:

Of what value (column) do you want the average and median? The date and
time?

You can use the aggregate function AVG() for the average.

The medial will use a query in which you count the rows, divide that by two,
and then find the Nth row in some sequential order where N is that value.
So, of 7 values, you want the 4th. If there are 8 values, do you want the
4th or the 5th? Either is equally "median" to the set.

Tom Ellison

 

[Douglas J. Steele]

I was always taught that the median of an even number of values was the
average of the two middle values (so the average of the 4th and 5th values
in your example)

My October, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access" dealt with measures of central tendency. You can download the column
(and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Tom Ellison]

Dear Doug:

I have heard of this as being one of the alternatives. In some cases, it is
important that the median be a member of the set. There are alternatives.

Your's is certainly one of the major solutions to the problem.

Tom Ellison

 

[BJ]

Hi ,

I want the median to be calculated on the Rainfall column as selected
below.

SELECT Year(tblRainFallData.TimeStamp) AS MyYear,
Month(tblRainFallData.TimeStamp) AS MyMonth, COUNT(*), (COUNT(*)*.2) AS
Rainfall, tblRainFallData.SiteName
FROM tblRainFallData
GROUP BY Year(tblRainFallData.TimeStamp),
Month(tblRainFallData.TimeStamp), tblRainFallData.SiteName;


This is my query. This gives me monthly rainfall of a site say 'BOW'.
I want to calculate the median and average rainfall for say January,
then february and so on.

On the above query I fetch all the monthly data at once. Then how do I
calculate the median on the data in the query itself ?

Any help is appreciated.

Thanks BJ

If I combine the days rainfall doing a grouping.

 

[BJ]

Hi ,

I want the median to be calculated on the Rainfall column as selected
below.

SELECT Year(tblRainFallData.TimeStamp) AS MyYear,
Month(tblRainFallData.TimeStamp) AS MyMonth, COUNT(*), (COUNT(*)*.2) AS
Rainfall, tblRainFallData.SiteName
FROM tblRainFallData
GROUP BY Year(tblRainFallData.TimeStamp),
Month(tblRainFallData.TimeStamp), tblRainFallData.SiteName;


This is my query. This gives me monthly rainfall of a site say 'BOW'.
I want to calculate the median and average rainfall for say January,
then february and so on.

On the above query I fetch all the monthly data at once. Then how do I
calculate the median on the data in the query itself ?

Any help is appreciated.

Thanks BJ