SQL AVG Query Needing assistance

Question

My question is i'm trying to find the average time length a call has been open for.

So heres my columns, " ID Opened Contact Category SubCategory Cause Description TakenBy AssignedTo Closed " So i want my query to add an extra column on to these results which shows the average time a call is open for before its closed, calculated by the time between the open date and closed date.

My query is as shown:

Select  ID, Opened, Contact, Category, SubCategory, Cause, Description, TakenBy, AssignedTo, Closed
From TCall
where CustID = 163
and Opened between '2013-09-01 00:00:00.000' and '2014-09-01 00:00:00.000'

Anymore information required, please ask me.

Use `AVG(Closed-Opened) As AverageTime` in the query. AVG will return a single value. Do you want to use the same single value as an extra column for all the rows — Ram, Sep 02 '14 at 14:58
what do you mean you want the last column to be the average time? Do you mean the actual time per call? You can perform an avg on the newly created column separately, but i would thing you would just want a datediff in seconds between start and end — Kritner, Sep 02 '14 at 15:00
Yes i was looking to use the single value as an extra columb for all the rows, thanks for your help! — surGe, Sep 02 '14 at 15:05
possible duplicate of [Using SUM() without grouping the results](http://stackoverflow.com/questions/11357640/using-sum-without-grouping-the-results) — Bulat, Sep 02 '14 at 15:15

score 0 · Answer 1 · answered Sep 02 '14 at 15:06

You could do something like this: (assuming ms sql)

select  avg(secondDuration)
from (
    Select  ID, Opened, Contact, Category, SubCategory, Cause, Description, TakenBy, AssignedTo, Closed, dateDiff(s, opened, closed) as secondDuration
    From TCall
    where CustID = 163
    and Opened between '2013-09-01 00:00:00.000' and '2014-09-01 00:00:00.000'
) subQuery

SQL AVG Query Needing assistance

1 Answers1