REQ: SQL question

[Robert]

I have a SQL statement to look if there is an occurrence of double date/time
values and how many occurrences.
Now i need a SQL statement to get the average of the total number of
occurrences.

This is my SQL statement:

SELECT First(DVO.DateTime) AS [DateTime], Count(DVO.DateTime) AS
NumDuplicates
FROM DVO GROUP BY DVO.DateTime HAVING (((Count(DVO.DateTime))>1));

(I use the SQL statement with VB6)

Thanks in advance,

Robert

 

[Guest]

I guess the question is what do you do with the results of the query once it
completes?! Do you populate some list?

If you are looping through the results, then just keep two values: a record
counter and an occurrence total.

Once the looping has completed, divide the occurrence total by the record
counter and you have your average.

That I believe is going to be your best solution.

 

[Wart]

Robert,
Add the following to your SQL statement
Compute Avg(Count(DVO.DateTime))
You will get back two result sets, the second of which will have the
average.

Also, the query you are sending will omit any records with a count of one
which will skew your average. You might not want this.
HTH,
CF