Average of Duration coming in hhh:mi:ss format

[infernalhell]

I would like to find the average of duration of the times coming in the below format (hhh:mi:ss)

20:32:37
67:55:50
58:36:35
106:32:11
91:30:39

Any help is greatly appreciated

[GJColeman · Accepted solution]

You could do it the long way, multiplying hours by 3600, etc., or simply cast these durations as intervals.  For example, you can do this in BTEQ or Studio:

create table times (id int, hms interval hour(3) to second) unique primary index(id);

insert into times values(1, '20:32:37');
insert into times values(2, '67:55:50');
insert into times values(3, '58:36:35');
insert into times values(4, '106:32:11');
insert into times values(5, '91:30:39');

select avg(hms) from times   produces:  69:01:34.400000