Average no of records per day

[Steevp]

Hi,

I have a table that have a number of records created each day with a field showing the days date. I need to show the average per day and the maximum number entered in a day. I am using VBScript and trying to pull the info with an SQL statement and it is driving me mad. Your help would be appreciated.

Thanx

 

[IFELSETHEN]

Hi.... in this example I used 'test' as the table name with two fields (date, rec_num).
*******************************************************
SELECT date, max(rec_num) as max_num, avg(rec_num) as avg_num FROM test GROUP BY Date
*******************************************************

John

*********************
John Nyhart

http://www.nyhart.com

*********************

 

[Steevp]

Thanks for your help John,

However, I am not getting the answer I would expect. The table has records over a number of years. the rec_no field in incremental for the whole table (i.e. 1980 - 24800, but with gaps - presumably where records have been deleted) If I add a Where clause to show only the records for say 1997 (i.e. WHERE Right([Date of Stop],2)=97) there are 4343 records. Using the Select statement you suggest with the WHERE clause gives and average of 7275(!). I'm sure that there is a simple answer to this, but it is driving me mad.

I have to repeat the exercise monthly for a large number of databases (>100) and need to get it right.

Thanks

 

[IFELSETHEN]

Bummer ..... that's were an Active Flag would be better then deleting the record. Maybe you need to use the old fashion avg. Total / Number?
*****************TRY THIS******************
SELECT date, max(rec_num) as max_num,
sum(rec_num)/count(rec_num) as arg_num1
FROM test GROUP BY Date
********************************************
John


*********************
John Nyhart

http://www.nyhart.com

*********************

 

[Steevp]

Hi John,

Still no good (I'm not sure about that sum clause). This is what I am using:

SELECT Sheet1.[Date of Stop], max([Rec No]) as max_num, sum([Rec No]) as arg_num1 FROM Sheet1 WHERE (((Right([Date of Stop],2))="97&quot)
GROUP BY Sheet1.[Date of Stop], Sheet1.[Rec No];

A sample of the data below (the table features dates from all years 1997 - 2003:
Rec No Date of Stop
2558 21/07/97
2559 21/07/97
2561 20/07/97
2562 21/07/97
2563 21/07/97
2564 20/07/97
2565 20/07/97
2566 18/07/97
2567 20/07/97
2568 19/07/97
2569 21/07/97
2570 20/07/97
2573 18/07/97

Thanks in advance
BTW - like the scrolling feature on your web site

 

[IFELSETHEN]

Hi .... I guess that you have lost me in what you are trying to get as results.

.... did the web site about two years ago .... all the movement is done with CSS.

John

*********************
John Nyhart

http://www.nyhart.com

*********************

 

[Steevp]

Hi John,

I would like to get the answer to two questions:

For a given year (e.g. 1997) - the average no. of entries per day and the maximum no. of entries in a day

For this sample (not the best selection) the results should be 5.75 (23 entries/4 days) and 5 (on both the 20/07/97 and 21/07/99). Entries are not made every day and the daily entries are not necessarily made in order (hence the unordered list - some are entered retrospectively). I had no hand in the original system (honest!)

I pointed out your site to a friend of mine who particularly likes that sort of thing - though in his case good taste is usually forgotten in the rush for features

He is trying to work out how you did it - I will leave him to wallow for a while

Thanks

 

[dodge20]

I would probably break this down into 2 queries.

For the avg no of entries per day, you are going to have to do a count of all the entries and divide that by a count of days.

For the maximum no of entries, you will need to use a having clause

HAVING Count(tblNAME.[field])
= ( select TOP 1
Count(tblNAME.[field])
FROM tablName

Dodge20