# Thread: Average Help

1. Registered User
Join Date
Apr 2006
Posts
12

## Unanswered: Average Help

if I have a bunch of times, and want an average of them, how do I do that?

Thanks.

For more details, see this thread:

http://www.dbforums.com/showthread.php?t=1215633

2. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
use the AVG aggregate function (with GROUP BY if you want an average per something, without GROUP BY if you want the average for the whole result set)

3. Registered User
Join Date
Apr 2006
Posts
12
That doesn't work for arguments of the type datetime.

4. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
Originally Posted by jman5436
That doesn't work for arguments of the type datetime.
you shoulda said that already in post #1

can you please tell me what average you expect for the following datetime values --

september 15, 1975
october 23, 2006
april 1, 1999

5. Registered User
Join Date
Apr 2006
Posts
12
.. I dunno

Whatever the middle date is...

There should be a way to do it, no?

6. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
i guess what i was trying to point out is that averaging datetimes doesn't make sense

you could try SELECT AVG(CAST datetimecol AS INTEGER) but would you really recognize 34303 as a valid answer?

okay, you could cast that back as datetime, and you'd get December 2, 1993

is that acceptable?

7. Registered User
Join Date
Apr 2006
Posts
12
Well, if you look @ the forum that I posted the url to, I actually wanted an average of differences. I have a start time, and a stop time, and I subtracted them, but the result was still a date-time format so I couldn't take the average of it. I want the averages of the difference in times, I dunno what format yet (ie, days, hours, minutes, seconds).. How is it done in minutes?

Thanks

8. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
my advice for finding the average of a number of date differences is to calculate the differences using the DATEDIFF function with seconds as the datepart parameter

note: "DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years." (BOL)

take the average, and then convert the result back into whatever format you wish

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Provided Answers: 1
Originally Posted by jman5436
Well, if you look @ the forum that I posted the url to, I actually wanted an average of differences. I have a start time, and a stop time, and I subtracted them, but the result was still a date-time format so I couldn't take the average of it. I want the averages of the difference in times, I dunno what format yet (ie, days, hours, minutes, seconds).. How is it done in minutes?

Thanks
The average of the differences is just the Max minus the Min divided by the number of data pairs:
Code:
```select	(Max([DateValue])-Min([DateValue]))/(count(*)-1) as AverageDifference
from	[YourTable]```
or this, which is probably better:
Code:
```select	DateDiff(minute, Min([DateValue]), Max([DateValue]))/(count(*)-1) as AverageDifference
from	[YourTable]```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•