current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

I have an issue, we have trac postgresql db (version 8.4) and we need to get the worklog based on the tags(keywords) in tickets and group time spent on these tickets.

This is my query:

select round(sum(wl.endtime-wl.starttime)/3600.0, 2) AS sum_of_hours, 
      string_to_array(t.keywords, ',') AS keywords 
from work_log AS wl 
  JOIN ticket AS t ON t.id = wl.ticket 
where t.keywords SIMILAR TO '%SWA.IMPLEMENTATION%' 
GROUP BY t.keywords 
HAVING string_to_array(t.keywords, ',') @> ARRAY['SWA.IMPLEMENTATION'];

The output is:

     sum_of_hours |       keywords
--------------+------------------------------
       950.08 | {Running,SWA.IMPLEMENTATION}
        11.00 | {SWA.IMPLEMENTATION,Done}
       341.63 | {SWA.IMPLEMENTATION}
        49.25 | {SWA.IMPLEMENTATION,Running}
(4 rows)

My goal is to group all hours where "SWA.IMPLEMENTATION" is presented. So all those 4 lines should be group together.

Thanks for any advice.

share|improve this question
1  
An example on sqlfiddle would be nice. – Jakub Kania Jan 30 at 8:19
    
Oh, and looking at your query it seems like you only need one group in the output, are there any more or is that it? – Jakub Kania Jan 30 at 8:31
    
Hi, thanks for the reply. Sorry I'm quite new at postgres and the database is under the confidental covering of processes in our company. There are more key words in the row keywords, so my problem is that I need to find the key word (as SWA.IMPLEMENTATION) in the row and if there will be this word count the hours in this line. Sorry for my english and my low knowledge of the postgres. Thanks a lot. – Matesack Jan 31 at 22:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.