CODEWITHSUNDEEP
mysqlsql
Codded
Codded

Reputation: 1256

MySQL JOIN by SELECT earliest DATETIME

This query groups tickets by who they are assigned to and works out the average rounded number of days the ticket has taken to be closed.

SELECT a.id as theuser, round(avg(DATEDIFF( ta.dateClosed, t.dateAded ) * 1.0), 2) as avg
FROM tickets t join
     mdl_user a
     on find_in_set(a.id, t.assignedto) > 0
GROUP BY a.id ORDER BY avg ASC

I would now like to JOIN the ticketanswer table to find out the average time for first response. The ticket could have multiple answers so i just want to get the first one. Therefore I have tried to change the query to include this with no avail. Could anyone shed a light as to what im doing wrong?

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: 1

Views: 399

Answers (1)

SQLChao
SQLChao

Reputation: 7837

Made some slight modifications to your query.

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 ticketid, MIN(dateAded) as started FROM ticketanswer GROUP BY ticketId) ta ON t.id = ta.ticketId
GROUP BY a.id ORDER BY avg ASC

Upvotes: 1

Related Questions