I can get a list of statuses that a tracking ticket has had, as well as the id of those statuses, as follows:
SELECT t.id, t.name, GROUP_CONCAT( CONCAT(s.id, ':', s.name) ORDER BY s.id) AS all_statuses_history FROM tracking t LEFT JOIN changelog c ON t.ID = c.tracking_id LEFT JOIN statuses s ON c.changed_to = s.id
That will have duplicates (status can bounce around in the real world), so I change the
GROUP_CONCAT(DISTINCT CONCAT(s.id, ':', s.name) ORDER BY s.id) AS .. (etc)
That's better, but what I'd REALLY like is the id, status name, and count of instances, looking something like this:
1:Requested(1), 2:Pending(3), 3:On Hold(2), 4:Completed(1)
You'd read that as "the ticket bounced around between pending and on hold a bit and was finally completed".
I can't put
COUNT(*) or apparently
GROUP BY inside of a GROUP_CONCAT function. Is it possible to get all of this information inside of a GROUP_CONCAT?