Post reply

How to find average, min, max for time field

  • March 4, 2011 at 10:02 am

    #233611

    i need to find avg, min and max for the responsetime in a table.

    For example i just created a table

    CREATE TABLE #URL_Requests

    (id int,

    URI varchar(10),

    response_time time(4),

    date_time smalldatetime)

    INSERT INTO #URL_Requests values

    (1, 'Sri', '11:45', '2011/02/01')

    INSERT INTO #URL_Requests values

    (1, 'Sri', '12:45', '2011/02/01')

    INSERT INTO #URL_Requests values

    (1, 'Sri', '13:45', '2011/02/01')

    INSERT INTO #URL_Requests values

    (1, 'SAR', '11:45', '2011/02/02')

    INSERT INTO #URL_Requests values

    (1, 'SAR', '12:45', '2011/02/01')

    INSERT INTO #URL_Requests values

    (1, 'SAR', '1:45', '2011/02/01')

    When i wtite Avg(ResponseTime), it gives me error. So can somebody plz help me out.

  • March 4, 2011 at 10:50 am

    #1294807

    Something like this?

    SELECT

    id,

    uri,

    CAST(DATEADD(ss,AVG(DATEDIFF(ss,0,response_time)),0) AS TIME) AS avg_response_time

    FROM #URL_Requests

    GROUP BY id,uri

    You cannot average a time data type. Therefore, it needs to be converted into a numeric value. The example I used will be accurate to the second. If you only need minutes, you should change it accordingly.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply