获取sql中日期差异和的平均值

I have transaction table like this:

我有这样的交易表:

transactID           Locid       vtid        dtime                    Paydate
20                   5           7           2013-05-07 17:40:42.000  2013-05-07 17:55:42.000
21                   5           7           2013-05-07 18:15:17.000  2013-05-07 18:25:17.000
22                   5           7           2013-05-07 18:27:44.000  2013-05-07 18:47:44.000
23                   5           8           2013-05-08 12:53:54.000  2013-05-08 13:05:24.000
24                   5           8           2013-05-08 13:11:21.000  2013-05-08 16:53:03.000

I wrote the query like this to get SUM of datediffernce:

我写这样的查询是为了得到date差分的和:

SELECT convert(varchar(10),sum(DATEDIFF(hour,t.Paydate,t.DelDate)))+':'   +convert(varchar(10),sum(DATEDIFF(minute,t.Paydate,t.DelDate)% 60)) + ':'
 +convert(varchar(10),sum(DATEDIFF(SECOND,t.Paydate,t.DelDate)% 60)) 
AS ' HH:MM:SS'
FROM Transaction_tbl t  
WHERE t.Locid=5 
GROUP by vtid 

Now I am getting output as sum of datediffence: HH:MM:SS 3:44:73 1:8:67 I want to get the output as average of this value .I mean the first answer(3:44:73) is the sum of vitd 7,instead of getting SUM I want to get average of sum, here total 3 times vtid 7 is repeating. So answer divide by 3.
Is there any way to do get average like this?

现在我得到输出的总和datediffence:HH:MM:SS 3:44:73 1:8:67我想这个值的输出平均,我的意思是第一个答案(3:44:73)vitd 7的总和,而不是和我想的平均和,这里总3次vtid 7是重复。答案除以3。有什么办法达到这样的平均水平吗?

1 个解决方案

#1

Try this:

试试这个:

select SUM(DATEDIFF(MI,t.Paydate,t.DelDate)) as sum_min,
       AVG( CONVERT(NUMERIC(18,0), DATEDIFF(MI,t.Paydate,t.DelDate) ) ) as avg_min
from Transaction_tbl t where t.Locid=5
group by t.vtid