MySQL – The GROUP_CONCAT() function

GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.

For example if you query:

mysql> SELECT Language FROM CountryLanguage WHERE CountryCode = 'THA';

It outputs:


To concatenate the values into a single string, you query:

mysql> SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

Then the output will be:

Chinese, Khmer, Kuy, Lao

You can also use some format of GROUP_CONCAT(). Like

  • SELECT GROUP_CONCAT( Language SEPARATOR ‘-‘ )… It will use ‘-‘ instead of ‘,’
  • SELECT GROUP_CONCAT( Language ORDER BY Language DESC )… To change the order and shorting output

One thing to remember: GROUP_CONCAT() ignores NULL values.


37 Responses to “MySQL – The GROUP_CONCAT() function”

  1. Parveen Sharma Says:

    update admin_messages
    set subject=’function’
    where messageId = (select max(messageId) from admin_messages)

    this query is create error plz. solve this query solution


    Parveen Sharma

    • sharmi Says:

      It is not possible to update the same table and use that in the from clause in your subquery. It could be rewritten using joins

  2. Parveen Sharma Says:

    possible more than row result in this format example

    name marks
    abc 36
    xyz 56
    def 76
    abc 86
    abc 66
    def 76

    Result is
    abc xyz def
    36 56 76
    86 76


    Parveen Sharma

    • Don M Says:

      Did you ever get a query to accomplish this? I’m looking to do something quite similar and am unsure how to do it.

  3. sajjadul robin Says:

    I think group_concat() has actually different purpose:
    e.g. To group or show the people who lives in same address, or show the students who have same score.

    query : select student, group_concat(score) from student group by student_name;

    and if you want to group which has more than one people, the query can be:
    select student, group_concat(score) from student group by student_name having count(student_name) > 1;

    I need this email for one of my projects.

    Happy to share here 🙂

  4. James Says:

    Thanks for the write up, very helpful.

  5. oasisfleeting Says:

    Thanks a ton!! This is exactly what I was looking for!

  6. andrew Says:

    Thanks man

  7. Daniel Z. Says:

    This was useful, thanks! In addition to the above examples, you can use the DISTINCT keyword to get only unique values within the GROUP_CONCAT. e.g. GROUP_CONCAT(DISTINCT Languages)

  8. Constant Marcove Says:

    thanks much this is most enlightening Parveen,

    is there a function that does the reverse of group_concat
    ie. from languages: english,french,chinese
    to generate separate rows as follows:

  9. Constant Marcove Says:

    reverse group_concat in order to resolve the following:

    table1 project=A lang=chinese,french,english,spanish
    table2 lang texte
    ==== ====
    spanish ola
    french bonjour
    german hallo

    i need to do a join of the 2 tables to obtain only the records of table2 based on the languages in table1 from record project=A which has lang=chinese,french,english

    such that the result would be
    lang texte
    ==== ====
    french bonjour
    spanish ola

    thanks much,

  10. regx Says:

    Wonderfully useful.
    I just used this to create a simple function for looking up users based on profile information in Drupal 6.

  11. double contract beds Says:

    I had been really glad to learn your internet site, it features specifically the content I’m planning to locate.

  12. asdas Says:

    Thanks useful information

  13. Synthetic Wigs Says:

    False Hairpiece Com their wide selection of make Synthetic Flowing hair Hair pieces, Particularly long Imitation Wigs, Reasonably priced Fake Wigs, Fast Fabricated Wigs, Internal Fabricated Hairpiece and

  14. saefullohrifai Says:

    keren. terima kasih..

  15. Rohidas Says:

    Grete Post very Helpful function

  16. Arpit Says:

    Thanks, Very easy to understand via example.

  17. Arif Says:

    Thanks Qiu…
    very usefull…

  18. Says:

    After exploring a number of the blog posts on your website, I seriously like your technique of
    writing a blog. I saved it to my bookmark webpage list and will be checking back soon.
    Please visit my website too and tell me what you think.

  19. Says:

    I really desired to discuss this particular post,
    Roman Shades “MySQL – The GROUP_CONCAT() function | Think Different” together with my own pals on facebook itself.

    Ijust simply planned to disperse ur terrific writing!

    Many thanks, Geneva

  20. binary options trading systems Says:

    Someone necessarily assist to make critically articles I would state.
    This is the first time I frequented your website page and to this point?
    I amazed with the analysis you made to create this actual
    post incredible. Wonderful job!

  21. accounting for stock options issued to non employees Says:

    I’m very happy to find this page. I need to to thank you for your time due to this wonderful read!! I definitely enjoyed every little bit of it and I have you bookmarked to see new things on your site.

  22. orquesta Says:

    Great post. I was checking constantly this weblog and I am impressed!
    Very helpful info specially the final phase 🙂 I take care of such information much.
    I used to be seeking this certain info for a very long time.
    Thank you and good luck.

  23. Leandra Says:

    “MySQL – The GROUP_CONCAT() function | Think Different” was a superb
    blog post, can’t help but wait to read through more of ur posts.
    Time to waste numerous time on-line lol. Thanks for your effort ,Victorina

  24. Elinor Says:

    This particular blog, “MySQL – The GROUP_CONCAT() function |
    Think Different” was in fact outstanding.
    I am printing out a duplicate to demonstrate to my close friends.
    Thanks for the post-Reyes

  25. Sitthykun Says:


  26. Says:

    Excellent post. I was checking continuously this weblog and I’m impressed! Very helpful information particularly the ultimate section 🙂 I care for such info much. I used to be looking for this certain information for a very lengthy time. Thank you and best of luck.

  27. Salah Deen Says:

    How can I add a hyperlink to the comma separated values? Imagine I use as the separator can i use those as individual hyperlinks?

  28. Says:

    Hi there very nice site!! Man .. Excellent ..

    Wonderful .. I’ll bookmark your web site and
    take the feeds also? I’m satisfied to find a lot of useful information here in the post, we’d like develop more strategies
    on this regard, thanks for sharing. . . .
    . .

  29. samkam Says:


  30. samkam Says:


  31. kleines wohnzimmer einrichten ideen Says:

    What i don’t understood is in fact how you’re now
    not really a lot more smartly-preferred than you might be now.
    You are very intelligent. You realize therefore considerably with regards to
    this subject, produced me in my opinion believe it from numerous varied angles.
    Its like women and men don’t seem to be fascinated until it is something to accomplish with Woman gaga!
    Your individual stuffs great. Always maintain it up!

  32. PraVeeN Says:

    I am having problem to search data from two tables where both fields have comma separated values in different order.

    For instance, I have two tables:
    1. Users
    id gender ethnicity
    1 male asian,american,african
    2 female asian,african,american
    3 female any

    2. Roles
    id ethnicity
    25 american,asian,african
    102 african,american,asian
    451 any
    402 any,pecific islander

    Now, I want to fetch data from both the tables on the basis of “ethnicity” field. Condition is, at least one value should match from both the fields. Here after comparing both the tables there may be multiple records we may have but I need one record for same user and same role. If same user will have same role and will have multiple records then we need one of all records.

    I did R&D but didn’t find anything for this case. So please help me out for this and give me your best solution for this as soon as possible. I hope I will get a solutions from your side for sure.

    Thanks in Advance.. 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: