T-SQL calculating average time

Question

I have a problem with calculating average time. This is the case: i have multiple rows, in each row there is data in time format so I need to calculate an average time of all rows and multiply it with number of rows but of course I have a problem with data format because I need to multiply time with integer Can somebody help me with some advice? thnx Here is some data: ``` times 00:00:00.7400000 00:00:01.1870000 00:00:00.6430000 00:00:00.6100000 00:00:12.9570000 00:00:01.1000000 00:00:00.7400000 00:00:00.5300000 00:00:00.6330000 00:00:01.6000000 00:00:02.6200000 00:00:01.0300000 00:00:01.9630000 00:00:00.9800000 00:00:01.0170000 00:00:00.7600000 00:00:00.7130000 00:00:00.9730000 00:00:01.0000000 00:00:01.0530000 00:00:02.9400000 00:00:00.8200000 00:00:00.8400000 00:00:01.1800000 00:01:25.8230000 00:00:01.0000000 00:00:00.9700000 00:00:01.2930000 00:00:01.3270000 00:00:13.5570000 00:00:19.3170000 00:00:58.2730000 00:00:01.6870000 00:00:18.7570000 00:00:42.8570000 00:01:12.3770000 00:00…

Accepted Answer

You should be able to use something similar to the following:

```
select 
  cast(cast(avg(cast(CAST(times as datetime) as float)) as datetime) as time) AvgTime,
  cast(cast(sum(cast(CAST(times as datetime) as float)) as datetime) as time) TotalTime
from yourtable;
```

See SQL Fiddle with Demo.

This converts the `times` data to a `datetime`, then a `float` so you can get the `avg`/`sum`, then you convert the value back to a `datetime` and finally a `time`

T-SQL calculating average time

Solution

Problem