Complex SQL query to combine rows

Question

I have 2 tables first is

Thread { code, itr_global,campaign, contact, start_time,duration}
segment {code,thread,start_time,duration,state}

There are multiple other joins but these 2 are major joins. 2 table are realted as thread.code=segment.thread.

In segment table there will be multiple rows for singl thread. I need to get values

campaign start_time duration waititme talk_time hold_time wrap_time

Where wait_time I can get as segment.state=7 & talke_time=segment.state=6 & wrap time as segment.state=8 I am not able to get all these values in single row as it will give me 3 diffrent rows for each record. How can I get all the values in single row as per above format.

Answer 1

You need some conditional aggregation SUM(CASE...) to get the result you want.

All this is based on my guesses about the structure and meaning of your tables, which you omitted from your question.

SELECT thread.code, thread.campaign,
       SUM(segment.duration) duration,
       SUM(CASE WHEN segment.state = 7 THEN segment.duration END) waittime,
       SUM(CASE WHEN segment.state = 6 THEN segment.duration END) talk_time,
       SUM(CASE WHEN segment.state = 8 THEN segment.duration END) wrap_time,
       42 hold_time, -- you didn't say how to get hold_time
       SUM(CASE WHEN segment.state = 8 THEN segment.duration END) wrap_time
  FROM Thread
  LEFT JOIN segment ON Thread.code = segment.thread
 GROUP BY  thread.code, thread.campaign