0

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?

  • Yes, it is possible. But you must understand that you need 2 grouping levels - first will count status instances, and second will concat ticket statuses and counts. The first level may be performed in a subquery, if your server version is 5+, or it can be performed using window function (COUNT() OVER) or in WITH clause, if your server version is 8+. Specify your server version in a tag. – Akina Oct 23 '18 at 8:53
  • I added the version tag 5.5 (actually + but not 8 yet). If you could provide an example, I couldn't figure out how to get a count besides a group by, but if I did a group by I'd split the rows and lose the ability to group_concat. – Oliver Williams Oct 23 '18 at 9:55
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.