MySQL, would like to get both a field and a count inside GROUP_CONCAT

Question

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 with DISTINCT keyword:

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?

Answer 1

As a schema:

SELECT name, 
       GROUP_CONCAT(CONCAT(status_id, ':', status_name, '(', status_cnt, ')')) AS all
FROM ( SELECT name, 
              status_id, 
              status_name, 
              COUNT(status_name) AS status_cnt
       FROM source_tableset
       GROUP BY name, -- first level GROUP BY and counting
                status_id, 
                status_name ) AS subquery
GROUP BY name -- second level GROUP BY and group concatenation