MySQL GROUP_CONCATIn the very early days of Percona, Vadim wrote very nice post about MySQL GROUP_CONCAT (GROUP_CONCAT).

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

When is MySQL 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.

The following are 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:

“; foreach($rows as $row) { echo “‘.$row[“company”].”.$row[“description”].”.$row[“engineer_list”].’ ‘; } echo ”


“; $result->close(); $mysqli->close(); ?>


1OTFix replicationMiguel Nieto
2PZHelp with installation of Percona ClusterMichael Rikmas
3VKHardware suggestionsMarcos Albe, Michael Rikmas
4FDError: no space leftMarcos Albe, Michael Rikmas, Miguel Nieto, Valerii Kravchuk
5ASHelp with setup daily backup by XtrabackupMarcos Albe, Miguel Nieto, Valerii Kravchuk
6SSPoke sales about Support agreement renewalMarcos Albe
7FDAdd more accounts for customerMiguel Nieto, Valerii Kravchuk
8PZCreate Hot Fix of Bug 1040735Marcos Albe, Michael Rikmas
9OTQuery optimisationMarcos Albe, Miguel Nieto
10OTPrepare custom build for SolarisMiguel Nieto, Valerii Kravchuk
11PZexplain about Percona Monitoring pluginsValerii Kravchuk
12SSPrepare access for customer servers for future workMarcos Albe
13ASDecribe load balancing for pt-online-schema-changeMarcos Albe
14FDManaging deadlocksMichael Rikmas, Valerii Kravchuk
15OTSuggestions about buffer pool sizeMarcos Albe, Miguel Nieto

That’s a power of MySQL GROUP_CONCAT!

Share this post

Comments (22)

  • SiteKickr

    Good call on this article, I almost never hear anything about this really useful SQL aggregate function.

    October 22, 2013 at 9:01 pm
  • Gimmer

    Always worth remembering to set the session variable group_concat_max_len to a higher number if you are grouping excessively long lists

    October 22, 2013 at 10:31 pm
  • Peter (Stig) Edwards

    One thing to watch out for with GROUP_CONCAT (and ORDER BY) is how it can result in tmp tables on disk, the example query to list issues for each engineer above causes a tmp table on disk to be created:

    show session status like ‘Created_tmp_disk_tables’;
    | Variable_name | Value |
    | Created_tmp_disk_tables | 1 |

    set session group_concat_max_len=512;

    Stops the tmp table being created on disk. So does using ORDER BY NULL or removing the ORDER BY. Tested with mariadb 5.3.12, 10.0.4 and 5.6.13-rel61.0 Percona Server with XtraDB.

    The result type is TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY.

    Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

    Presence of a BLOB or TEXT column in the table

    Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes

    Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

    Also see: – When using GROUP_CONCAT() function with group_concat_max_len > 512 then the field type will be BLOB if ORDER BY is used, otherwise it will be VARCHAR.

    October 24, 2013 at 9:47 am
  • Michael Rikmas

    Thanks Peter for the comment. This is very interesting info which should be taken into attention.

    October 25, 2013 at 5:43 am
  • Roman Vynar

    Great topic, thanks!

    November 6, 2013 at 10:00 am
  • shamin

    whats the max length i can set for ” SET SESSION group_concat_max_len=15000000; ” is there any limit for the variable group_concat_max_len ?

    thanks in advance.

    March 27, 2014 at 5:43 am
  • Michael Rikmas


    Manual says it’s:
    for 32-bit systems: 4294967295
    for 64-bit systems: 18446744073709547520

    You can see details here:

    March 27, 2014 at 6:16 am
  • Umair Hamid

    Thank you sooooooooooooooooooooooooooooo many much, It Rocks

    April 3, 2014 at 8:35 am
  • Angelo Garcia

    Thank you very helpful article .

    September 15, 2014 at 12:49 am
  • Mikhail Batcer

    Thank you for the article.
    I ‘ve got a question. Why are you using multiple table names in FROM and conditions in WHERE, instead of JOINs?

    October 25, 2014 at 1:58 am
  • Will GM

    What about this scenario?

    JobNum OprSeq PartNum
    10000 10 N277070
    10000 10 N277070-MM
    10000 14 N277070
    10000 14 N277070-MM
    10000 14 N277070-001
    10000 16 N277070-MM
    10000 16 N277070
    10000 16 N277070-001
    10000 16 N277070-00024
    10000 20 N277070


    JobNum OprSeq PartNum PartNumItemTwo PartNumItemThree PartNumItemFour
    10000 10 N277070 N272770-MM NULL NULL
    10000 14 N277070 N272770-MM N277070-001 NULL
    10000 16 N277070 N272770-MM N277070-001 N277070-00024
    10000 20 N277070 NULL NULL NULL

    Is there a possible way to achieve this? I mean, is it even logical a cliente is asking for this?

    January 18, 2015 at 11:26 pm
  • Yogi Ghorecha

    Kudos to you..!!!

    February 2, 2015 at 10:38 am
  • Andrie Suak Tiwa

    Great Article!! GROUP_CONCAT really interesting feature

    May 22, 2015 at 3:24 am
  • Arpan Das

    Very helpful. Thank you Michael.

    June 26, 2015 at 3:31 am
  • peyman

    great article with full detail document

    June 28, 2015 at 8:20 am
  • acheronic

    great tutorial explaining the power of concat and group concat.
    thanks and kudos to you!

    August 13, 2015 at 12:56 pm
  • Fred

    If i want get all parents who have 2 AND 1 child id (or 2 only)
    How do it ?

    August 14, 2015 at 5:34 am
  • Sarah

    How about if you want to do something a bit smarter so that the results that did look like this

    5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk), 13 (Marcos Albe)

    turn into

    5 (Marcos Albe, Miguel Nieto, Valerii Kravchuk) and 13 (Marcos Albe)

    August 16, 2015 at 7:44 pm
  • Constantine Lazarakis

    Great article! Being a Java guy learning to use MySQL, you really contributed in me building faith in this environment! Thank you!

    August 24, 2015 at 12:17 pm
  • Pragam Srivastava

    Great topic, thanks!

    September 23, 2015 at 4:46 am
  • Edgard

    Thanks man!! Great post

    December 28, 2015 at 1:12 pm
  • Michiel

    would it be possible to add a AND a to the same group_concat ??

    I tried something like your example:

    GROUP_CONCAT(DISTINCT ‘‘ ‘‘, CONCAT(e_name, ‘ ‘, e_surname), ‘‘ ‘ ORDER BY e_name SEPARATOR ‘, ‘) AS engineer_list

    But that gives weird results. adding a , between the >’, ‘<span will not do better and '‘, Concat ….. does not work either.

    Is it possible at all and how to write it?

    January 10, 2016 at 9:04 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.