SQL query - SUM duration values (hh:mm:ss) from ALN field

Question

I have table T1 with IDs:

ID
1
2

I have table T2 with ID and GROUP_TRACKING time because record can be multiple times at specific group. GROUP_TRACKING time is ALN type (STRING) and this cannot be changed but it contains always duration value in hh:mm:ss where hh column always has at least 2 characters but of course it can contain and more characters in case when record has been is some group for very long period of time:

ID  GROUP   GROUP_TRACKING
1   GROUP1  05:55:05
1   GROUP1  10:10:00
1   GROUP2  111:51:00
1   GROUP2  01:01:00

So I need to made SELECT clause from T1 table and to join T2 table to track for each group (G1 and G2) how much time it spent for that specific group.

So the final result should be like this:

ID  GROUP1    GROUP2
1   16:05:05  112:52:00
2   null      null

How to make this SELECT SUM of these duration in hours and minutes calculation?

Thank you

....why do people do this to themselves? Why not make the darn thing a count of seconds and make it easier to query this stuff? It's not SARGable (b/c > 2 digits) and takes up twice the room. In any case, you're going to have to split the string on the delimiters, manually convert it to seconds, sum it, then convert it back to individual parts (since probably you don't want 60+ seconds, but rather 1 minute...). And then pivot it. Which, by the way, you have to know the columns ahead of time, or use some nasty dynamic sql. — Clockwork-Muse, Feb 12 '18 at 17:50
@Clockwork-Muse can you please help me with this SQL statement? Unfortunately this is out-of-the box column which is populated automatically by the system and I cannot change its definition. Thank you in advance — Veljko, Feb 12 '18 at 21:50
@Clockwork-Muse can you please write me this heavy SQL query? I am really not sure even how to start — Veljko, Feb 13 '18 at 08:25
....I gave you a list of steps you're going to have to do. Which one of them did you get stuck on? Were you not able to find information about how to do some part? — Clockwork-Muse, Feb 13 '18 at 16:33
@Clockwork-Muse Hi I get stuck with split and convert. I do not now how to start. Also I am not sure how to pivot it. Thank you in advance — Veljko, Feb 13 '18 at 16:47
Well, did [questions/answers like this one not work for you](https://stackoverflow.com/questions/18961996/how-to-split-a-string-value-based-on-a-delimiter-in-db2)? — Clockwork-Muse, Feb 13 '18 at 17:39

score 1 · Accepted Answer · answered Feb 16 '18 at 22:08

Here is a solution without the pivot step (which can be looked up in many other ansers)

with temp as (
select id
      , group
      , group_tracking
      , SUBSTR(group_tracking, 1,LOCATE(':',group_tracking)-1) * 3600 as First_PART_in_s
      , SUBSTR(group_tracking, LOCATE(':',group_tracking)+1,2) * 60 as Second_PART_in_s
      , SUBSTR(group_tracking, LOCATE(':',group_tracking, LOCATE(':',group_tracking)+1)+1, 2) as Third_PART_in_s
  from t2
)
select t1.id
      , t.group
      , int(sum(First_PART_in_s + Second_PART_in_s + Third_PART_in_s) / 360000) ||  replace(char(time('00:00:00') + mod(sum(First_PART_in_s + Second_PART_in_s + Third_PART_in_s),360000) seconds,ISO),'.',':') as duration
  from t1
  left join temp t
    on t1.id = t.id
  group by t1.id, group

I completly agree with @Clockwork-Muse that formats matter and using this inadequate format imposes lots of additional effort for reformatting or deconstructing and re-constructing things.

SQL query - SUM duration values (hh:mm:ss) from ALN field

1 Answers1

Linked