CODEWITHSUNDEEP
sqlt-sql
Michael Traveler
Michael Traveler

Reputation: 25

How to sum hours for duplicate values

i have sth like it in table in management studio

Name | hours
—------------
A    | 5:30
B    | 3:30
A    | 4:00
C    | 2:00
B    | 4:55

It is possible to add hours for A, B and C?

To result:

Hours is type: time(0)

Name | hoursxxx
—------------
A    | 11:30
B    | 8:25
C    | 2:00

I tried

Select 
       [Name], 
       Sum([hoursxx] ) as timeyyy 
from 
       [base].[dbo].[table]

But it completly dont work

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

It is painful to add hours as times. So add hours as decimal hours:

select name, sum(datediff(minute, 0, hours)) / 60.0
from t
group by name;

You can convert this back to a time:

select name, dateadd(minute, sum(datediff(minute, 0, hours)), convert(time, '00:00:00'))
from t
group by name;

But this runs the risk of overflow, because times are limited to 24 hours.

Here is a db<>fiddle.

Upvotes: 2

Related Questions