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?
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
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다