How to get sum of time field in SQL server 2008

Question

I am facing a problem in finding the sum of values stored in a column,

I have a table like this:

gs_cycle_no    | from_time   | to_time  | total_hours(varchar) ...
GSC-334/2012   | 13:00       | 7:00     |  42:00
GSC-334/2012   | 8:30        | 3:45     |  6:00
.
.
.

What i need to find is the Sum(total_hours) group by gs_cycle_no. But the Sum method will not work on the varchar column and also i cant convert it to decimal due to its format,

How can i find the sum of total_hours column, based on gs_cycle_no?

Answer 1

if you have no minutes and only hours, then you can do something like:

select
    cast(sum(cast(replace(total_hours, ':', '') as int) / 100) as nvarchar(max)) + ':00'
from Table1
group by gs_cycle_no

if you don't, try this:

with cte as
(
    select
        gs_cycle_no,
        sum(cast(left(total_hours, len(total_hours) - 3) as int)) as h,
        sum(cast(right(total_hours, 2) as int)) as m
    from Table1
    group by gs_cycle_no
)
select
    gs_cycle_no,
    cast(h + m / 60 as nvarchar(max)) + ':' +
    right('00' + cast(m % 60 as nvarchar(max)), 2)
from cte

sql fiddle demo

Answer 2

This will work:

;with times as (
    select gs_cycle_no = 'GSC-334/2012', total_hours = '8:35'
    union all SELECT gs_cycle_no = 'GSC-334/2012', '5:00'
    union all SELECT gs_cycle_no = 'GSC-334/2012', '16:50'
    union all SELECT gs_cycle_no = 'GSC-334/2012', '42:00'
    union all SELECT gs_cycle_no = 'GSC-335/2012', '0:00'
    union all SELECT gs_cycle_no = 'GSC-335/2012', '175:52'
    union all SELECT gs_cycle_no = 'GSC-335/2012', '12:25')
SELECT
    gs_cycle_no,
    hrs = sum(mins) / 60 + sum(hrs),
    mins = sum(mins) % 60
FROM 
    TIMES
    cross apply(
        select c = charindex(':', total_hours)
    ) idx
    cross apply(
        select
            hrs = cast(substring(total_hours, 1, c - 1) as int),
            mins = cast(substring(total_hours, c + 1, len(total_hours)) as int)
    ) ext
group by gs_cycle_no
order by gs_cycle_no