Results 1 to 9 of 9

Thread: Average Help

  1. #1
    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. #2
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    12
    .. I dunno

    Whatever the middle date is...

    There should be a way to do it, no?

  6. #6
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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. #8
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote 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]
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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