CODEWITHSUNDEEP
mysqlsql
Codded
Codded

Reputation: 1256

MySQL GROUP BY each comma separated value

Before anyone comments, I did not design this database with comma separated values :) I have spent time trying to find the answer but all I could find was GROUP_CONCAT() which seemed to do the opposite of what I wanted.

I would like to GROUP BY each of the values within the comma separated value field.

SELECT round(avg(DATEDIFF( dateClosed , dateAded ) * 1.0), 2) AS avg, department
FROM tickets GROUP BY assignedto

the assignedto field is the comma separated value field

row1 54,69,555
row2 54,75,555
row3 75,555

DESIRED OUTPUT: an average rounded figure for each value in assignedto field grouped.

EDIT - TRYING TO TAKE THIS TO THE NEXT LEVEL: I want to include the ticket answer table to get the first response for that ticket, use its datetime field to work out the average response time for each user.

SELECT a.id as theuser, round(avg(DATEDIFF( ta.dateAded , t.dateAded ) * 1.0), 2) as avg
FROM tickets t join
     mdl_user a
     on find_in_set(a.id, t.assignedto) > 0
INNER JOIN (SELECT MIN(ta.dateAded) as started FROM ticketanswer GROUP BY ta.ticketId) ta ON t.id = ta.ticketId
GROUP BY a.id ORDER BY avg ASC

Upvotes: 0

Views: 2412

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Yuck. You can do this, assuming you know the maximum number of assignments. Here is an approach:

select substring_index(substring_index(assignedto, ',', n.n), ',', -1) as assignedto,
       round(avg(DATEDIFF( dateClosed , dateAded ) * 1.0), 2) as avg
from tickets t join
     (select 1 as n union all select 2 union all select 3)
     on length(assignedto) - length(replace(assignedto, ',', '')) < n.n
group by substring_index(substring_index(assignedto, ',', n.n), ',', -1);

Or, an easier way if you have a list of assigned values, say in an AssignedTo table:

select a.assignedto, round(avg(DATEDIFF( dateClosed , dateAded ) * 1.0), 2) as avg
from tickets t join
     assignedto a
     on find_in_set(a.assignedto, t.assignedto) > 0
group by a.assignedto;

I'm sorry you have to deal with this malformed database structure.

Upvotes: 4

Related Questions