The power of MySQL’s GROUP_CONCAT

Posted on:



Share Button

In the very early days of Percona Vadim wrote very nice post about GROUP_CONCAT.

But I want to show you a bit more about it.

When is GROUP_CONCAT useful? Usually while working with Support customers I recommend it when you have aggregation of many-to-many info. It makes the view simpler and more beautiful and it doesn’t need much effort to make it work.

Some simple examples:

This is a test table:

Without grouping info the only way you can check things is:

But it looks much better and easier to read with GROUP_CONCAT:

Easy? Let’s go to production usage and some “real” examples :)

Assume you have 4 Support Engineers who were working with 6 Customers this week on 15 issues.

As it usually happens: everyone (sure, except those who are on vacation :)) worked on everything with everybody.

How you would represent it?

Here is my way:

Create test tables:

  • engineers (id, name, surname, URL) – list of engineers
  • customers (id, company name, URL) – list of customers
  • issues (id, customer_id, description) – list of issues assigned to customers
  • workflow (id, engineer_id, issue_id) – list of actions: issues and engineers who worked on them


List of issues for each engineer (GROUP_CONCAT):

List of engineers for each customer (GROUP_CONCAT inside of GROUP_CONCAT):

PHP/HTML? Why not? It’s easy :)

Source Code:


1 OT Fix replication Miguel Nieto
2 PZ Help with installation of Percona Cluster Michael Rikmas
3 VK Hardware suggestions Marcos Albe, Michael Rikmas
4 FD Error: no space left Marcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk
5 AS Help with setup daily backup by Xtrabackup Marcos Albe, Miguel Nieto, Valerii Kravchuk
6 SS Poke sales about Support agreement renewal Marcos Albe
7 FD Add more accounts for customer Miguel Nieto, Valerii Kravchuk
8 PZ Create Hot Fix of Bug 1040735 Marcos Albe, Michael Rikmas
9 OT Query optimisation Marcos Albe, Miguel Nieto
10 OT Prepare custom build for Solaris Miguel Nieto, Valerii Kravchuk
11 PZ explain about Percona Monitoring plugins Valerii Kravchuk
12 SS Prepare access for customer servers for future work Marcos Albe
13 AS Decribe load balancing for pt-online-schema-change Marcos Albe
14 FD Managing deadlocks Michael Rikmas, Valerii Kravchuk
15 OT Suggestions about buffer pool size Marcos Albe, Miguel Nieto

That’s a power of GROUP_CONCAT!

Share Button

Michael Rikmas

Michael joined Percona in October 2007. He serves in several roles, including Persona's 24x7 support coverage. He has an undergraduate degree in computer science, and in 2010 he started pursuing studies to earn an MBA.


Insight for DBAs, MySQL


Leave a Reply