GROUP_CONCAT useful GROUP BY extension

(There is an updated version of this post here)

MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT:

GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group.

Where it can be useful?

For example to get PHP array without looping inside PHP:

Table:

Handling in PHP:
old way:

with group_concat:

This should work faster, as we remove loop from PHP to MySQL server side.

Also it can be handy to use result concatenated string as part of IN statement:

Sure, last example can be handled with one query with joins, but sometimes we need the temporary ids in clients code, for example to execute query on another server.

One more thing: you may want to add ORDER BY NULL statement after GROUP_BY to avoid
unnecessary sorting with filesort

Share this post

Comments (51)

  • Chen Reply

    Thanks

    September 4, 2006 at 12:00 am
  • JWebuser Reply

    Hi Vadim,
    many thanks for this example!
    That helped me a lot!!!

    September 4, 2006 at 12:00 am
  • maxi Reply

    @bhushan .. thank you that resolved a query with bigint values!

    September 4, 2006 at 12:00 am
  • VT's Tech Blog Reply

    GROUP_CONCAT in MySQL…

    GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group.
    Where it can be useful?
    For example to get PHP array without looping inside PHP:
    CREATE TABLE services (
    id INT UNSIGNED NOT NULL,
    client_id …

    September 6, 2006 at 3:52 am
  • Symlinked » Blog Archive » GROUP_CONCAT useful GROUP BY extension Reply

    […] GROUP_CONCAT useful GROUP BY extension: MySQL has useful extension to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. […]

    September 7, 2006 at 12:44 pm
  • monk.e.boy Reply

    Nice info 🙂

    October 12, 2006 at 1:44 am
  • Vladimir Reply

    there is error in line 4 in example 3

    January 27, 2007 at 7:31 am
  • Vadim Reply

    Vladimir

    Thank you, fixed.

    January 31, 2007 at 12:28 pm
  • paul carey Reply

    A litte problem I had with the group_concat function was when selecting integers only I was getting a blob instead of a string to solve this I used a cast:

    SELECT GROUP_CONCAT(CAST(myInt as CHAR)) myInts FROM aTable;

    June 8, 2007 at 8:49 am
  • La fonction group_concat « Développement web Reply

    […] MySQL Performance Blog […]

    August 23, 2007 at 1:10 am
  • Edison2k3 Reply

    Hi, Thanks
    Do anybody know how to do the same as the GROUP_CONCAT() function do
    in MS-Access? It seems don’t have this kind of functions

    January 24, 2008 at 9:32 am
  • Chris Reply

    My sincere thanks to – 6. paul carey for publishing this fix – you saved my sanity.

    January 30, 2008 at 11:19 am
  • Rizwan Reply

    I have created a table (with only 2 fields) with the following query

    1.CREATE TABLE users (
    2.id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    3.name VARCHAR( 20 ) NOT NULL
    4.) ENGINE = MYISAM ;”

    There are 20,000 users in this table with ids from 1 to 20,000
    On executing the following query
    Code: ( text )

    1. select group_concat(id separator ‘,’) from users

    returns only 283 ids separated with ‘,’

    May 15, 2008 at 12:28 am
  • DHIRAJ Reply

    GROUP_CONCAT IS VERY NICE, BUT THERE IS A LIMIT UPTO 1024, CAN WE INCREASE THE LIMIT? IF YES PLEASE

    June 5, 2008 at 11:26 pm
  • Vadim Reply

    There is group_concat_max_len server variable, which by default is 1024

    June 6, 2008 at 3:43 pm
  • DHIRAJ Reply

    how to extent the limit of group concat > 1024

    June 10, 2008 at 8:52 pm
  • Vadim Reply

    group_concat_max_len=4096 in my.cnf
    or SET GLOBAL group_concat_max_len=4096

    June 11, 2008 at 10:16 am
  • DHIRAJ Reply

    where i can get my.cnf file or
    how to set global

    June 12, 2008 at 12:50 am
  • Eduardo Reply

    Hi, is it possible to use some kind of argument in group_concat that allows it to group 2 by 2? p.ex:
    SELECT id,client_id FROM services WHERE id = 3;
    +—-+———–+
    | id | client_id |
    +—-+———–+
    | 3 | 5 |
    | 3 | 6 |
    | 3 | 7 |
    | 3 | 8 |
    | 3 | 9 |
    | 3 | 10 |
    | 3 | 11 |
    +—-+———–+

    SELECT id,GROUP_CONCAT(client_id,2) FROM services WHERE id = 3 GROUP BY id;
    +—-+————————-+
    | id | GROUP_CONCAT(client_id) |
    +—-+————————-+
    | 3 | 5,6 |
    +—-+————————-+
    | 3 | 6,7 |
    +—-+————————-+
    | 3 | 7,8 |
    +—-+————————-+
    | 3 | 8,9 |
    +—-+————————-+
    | 3 | 9,10 |
    +—-+————————-+
    | 3 | 10,11 |
    +—-+————————-+
    Thanks!

    June 30, 2008 at 7:48 am
  • Kestas Reply

    Has MySQL rollup, cubes or grouping sets like Oracle?

    July 3, 2008 at 2:30 am
  • Murz Reply

    A litte problem I had with the group_concat function was when selecting integers only I was getting a blob instead of a string to solve this I used a CONCAT:

    SELECT CONCAT(GROUP_CONCAT(myInt),”) myInts FROM aTable;

    September 24, 2008 at 10:52 pm
  • JASAN Reply

    Murz,

    you saved my time. Thanks a lot!.

    January 6, 2009 at 6:32 am
  • Mysql - Caggie Reply

    @Murz, Jasan

    Alternatively you can use

    CONVERT(group_concat(myInt) USING utf8)

    January 27, 2009 at 6:24 am
  • Martin Zvarik Reply

    Don’t use!

    1) explode() can’t be faster than the loop

    2) GROUP BY slows the query

    Did you do benchmark, or you just guessed the results??

    January 27, 2009 at 6:10 pm
  • The coolest mysql function I didn’t know existed… » Karl Katzke | PHP, Puppies, and other Geekery Reply

    […] group_concat. To use it is to know it, and to know it is to love it. […]

    February 9, 2009 at 10:08 am
  • Florian 2L Reply

    Hello !

    I’ve a little problem using group_concat and user variable.
    I tried to set a group_concat(id) result into a @var, and then call the same table where id in(@var)
    but mysql return only the first row :\ do u have any idea ?

    Here is the code :
    ——————
    CREATE TABLE IF NOT EXISTS user (
    id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
    nom varchar(255) NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
    INSERT INTO user (id, nom) VALUES (1, ‘flo’),(2, ‘seb’),(3, ‘julien’),(4, ‘julie’),(5, ‘aymeric’),(6, ‘renaud’),(7, ‘lucy’),(8, ‘charlotte’),(9, ‘aurelien’);

    mysql> SELECT @lid := CAST(GROUP_CONCAT(id) AS CHAR) FROM user WHERE nom in(‘renaud’, ‘julie’, ‘lucy’);
    +—————————————-+
    | @lid := CAST(GROUP_CONCAT(id) AS CHAR) |
    +—————————————-+
    | 4,6,7 |
    +—————————————-+
    1 row in set (0.00 sec)

    mysql> SELECT @lid;
    +——-+
    | @lid |
    +——-+
    | 4,6,7 |
    +——-+
    1 row in set (0.00 sec)

    mysql> SELECT * FROM user WHERE id IN(@lid);
    +—-+——-+
    | id | nom |
    +—-+——-+
    | 4 | julie |
    +—-+——-+
    1 row in set (0.00 sec)

    I know it is totally useless but it’s just a sample 😉

    Regards.

    April 29, 2009 at 2:22 am
  • Baron Schwartz Reply

    Florian, this topic has been addressed hundreds of times on forums and mailing lists 🙂 Google is your friend.

    April 29, 2009 at 5:32 am
  • Florian 2L Reply

    Mysql support saved my time 🙂

    mysql> SELECT * FROM user where FIND_IN_SET(id, @lid);
    +—-+———-+
    | id | nom |
    +—-+———-+
    | 4 | julie |
    | 6 | renaud |
    | 9 | aurelien |
    +—-+———-+
    3 rows in set (0.02 sec)

    May 4, 2009 at 7:43 am
  • Barbara Reply

    I have two tables – member_directory & categories – that have a many-to-many relationship. I am using a bridge table called member_categories.

    SELECT DISTINCT(md.id),GROUP_CONCAT(categories.category SEPARATOR ‘, ‘) AS category FROM member_directory md LEFT JOIN member_categories mc ON md.id = mc.member_id LEFT JOIN categories ON mc.category_id = categories.id GROUP BY md.name ORDER BY md.isfeatured DESC, md.name ASC LIMIT 0, 10
    ====================================================================
    id | category |
    ——————————————————————–
    951 | CONDOMINIUMS, VACATION RENTALS |
    1711 | VACATION RENTALS, REALTORS |
    1413 | VACATION RENTALS |
    1163 | BOATING/FISHING/MARINE |
    2034 | VACATION RENTALS |
    2240 | TRAVEL AGENCIES, VACATION RENTALS |
    2033 | APARTMENT/HOUSE RENTALS |
    2208 | APARTMENT/HOUSE RENTALS |
    2153 | HEARING AIDS, MEDICAL SERVICES/HOSPITALS/CLINICS, AUDIOLOGY |
    1002 | CONDOMINIUMS |
    ====================================================================

    If I add any conditions regarding the category, the GROUP_CONCAT starts returning only the category mentioned in the WHERE clause (I don’t think I explained that very well, but here is an example). Adding a WHERE clause to the query:
    SELECT DISTINCT(md.id), GROUP_CONCAT(categories.category SEPARATOR ‘, ‘) AS category FROM member_directory md LEFT JOIN member_categories mc ON md.id = mc.member_id LEFT JOIN categories ON mc.category_id = categories.id WHERE mc.category_id = 187 GROUP BY md.id ORDER BY md.id ASC, md.name ASC LIMIT 0, 10

    ====================================================================
    id | category |
    ——————————————————————–
    1417 | SHOPPING |
    1736 | SHOPPING |
    1768 | SHOPPING |
    2219 | SHOPPING |
    2403 | SHOPPING |
    2407 | SHOPPING |
    2426 | SHOPPING |
    2431 | SHOPPING |
    2456 | SHOPPING |
    2458 | SHOPPING |
    ====================================================================

    Is there any way I can get the desired result? I just need a query to get information from the member_directory and any categories it may belong to.

    May 11, 2009 at 1:27 pm
  • Barbara Reply

    I was able to get what I needed by using a sub-query:

    SELECT DISTINCT(md.id), (SELECT GROUP_CONCAT(categories.category SEPARATOR ‘, ‘) AS category FROM member_categories LEFT JOIN categories ON member_categories.category_id = categories.id WHERE member_categories.member_id = md.id) AS category FROM member_directory md LEFT JOIN member_categories mc ON md.id = mc.member_id LEFT JOIN categories ON mc.category_id = categories.id WHERE mc.category_id IN (138) GROUP BY md.name ORDER BY md.isfeatured DESC, RAND(1283945655) LIMIT 0, 10
    ====================================================================
    id | category |
    ——————————————————————–
    2275 | VACATION RENTALS, MASSAGE THERAPY, CONDOMINIUMS |
    1388 | VACATION RENTALS |
    1839 | VACATION RENTALS, CONDOMINIUMS |
    1845 | CONDOMINIUMS, VACATION RENTALS |
    1808 | REALTORS, VACATION RENTALS |
    1899 | CONDOMINIUMS, VACATION RENTALS |
    951 | CONDOMINIUMS, VACATION RENTALS |
    1713 | VACATION RENTALS, CONDOMINIUMS |
    2240 | TRAVEL AGENCIES, VACATION RENTALS |
    2285 | VACATION RENTALS, CONDOMINIUMS |
    ====================================================================

    However, if anyone has any tips to make this better, please post! Thanks!

    May 11, 2009 at 2:53 pm
  • JayaLakshmi Reply

    Details

    The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

    SET [GLOBAL | SESSION] group_concat_max_len = val;

    Current values in Our server

    max_allowed_packet = 67107840
    group_concat_max_len = 1024

    Query:

    To how much extent ,I can increase this variable size .and what is the maximumm data limit of GROUP_CONCAT

    May 12, 2009 at 11:42 pm
  • PixelMe Reply

    When I am using in GROUP_CONCAT, It will list all the values associated wit the parent ID, But I need only few like limit in ordinary SQL statement. Anybody know how to do this in MySQL qeury?

    tblmovies
    MovieId MovieName
    1 The Take
    2 Fatal Contact
    3 Flashbacks of a Fool
    4 Teddy Bear
    5 Nanking

    tblgenres
    GenreId GenreName
    1 Action
    2 Horror
    3 Sci-Fi
    4 Documentary
    5 Drama
    6 History

    tblmovie_genres
    movieId GenreId
    1 2
    1 4
    1 5
    2 4
    4 5
    3 5
    5 6

    When I was selecting the Movie whith Id 1 GROUP_CONCAT will result Horror,Documentary, Drama ; But I want to get only 2 genres which is Horror,Documentary.

    Please somebody help me .

    Thanks.

    May 13, 2009 at 12:05 pm
  • Función GROUP_CONCAT de MySQL | Otro Blog Más Reply

    […] – Syntax Error y MySQL Performance Blog (mirar también los comentarios) Share this on del.icio.usShare this on […]

    October 19, 2009 at 1:06 pm
  • liki05 Reply

    hi PixelMe.

    you can use substring_index(group_concat(column),’,’,2). it will return the first 2 concated values.

    November 11, 2009 at 12:47 am
  • bhushan Reply

    thank bosss “@Murz, Jasan ”

    it work for me..

    @Murz, Jasan

    Alternatively you can use

    CONVERT(group_concat(myInt) USING utf8)

    February 18, 2010 at 2:40 am
  • maxi Reply

    @bhushan .. thank you that resolved a query with bigint values!

    April 4, 2011 at 7:42 am
  • van Reply

    @Barbara,

    thanks

    July 27, 2011 at 11:33 am
  • Herah Reply

    Thank you! this really helped alot!

    August 14, 2011 at 7:29 am
  • Panagiotis Reply

    Hi, this is a useful tutorial but i need some more help. In my table the column I use the GROUP_CONCAT is a foreign key from another table. So, i have to display PC1,PC2,PC3 for example and no 2,3,4 which are the values of my table. I tried with LEFT JOIN but I didn’t get a result. I’m trying to make an extra query inorder to take those names but nothing yet. Can you halp me a little bit?

    September 18, 2011 at 11:41 pm
  • mohammed Reply

    Thank you , Vadim Tkachenko
    i used group_concat in my query but i got i problem
    problem is

    mysql group_concat limit length is 1024 and i need to increase it ( i did it )

    (
    but is there is problem when i increase group_concat length to more than 1024
    )

    it work fine in localhost , but on the website , i got error -> need more premission to do this

    November 1, 2011 at 10:24 am
  • Ron Reply

    I have a table like
    userid testid attempts grade
    2 1 6,4,9,2,7,5,3,8,1 20,55,56,67,80,66,33,34,52

    I want to get the attempts in order like
    userid testid attempts grade
    2 1 1,2,3,4,5,6,7,8,9 52,67,33,55,66,20,80,34,56
    And my query looks like
    SELECT li.userid, li.testid, group_concat(li.attempts AS att, group_concat(li.grade) FROM testattempts GROUP BY testid

    Could anyone help me with this?

    December 25, 2011 at 12:19 pm
  • santu Reply

    Hi,
    Can we change the comma( , ) symbol to | symbol in group contact present in the mysql …

    appreciates a reply..

    Thank U

    April 6, 2012 at 11:28 pm
  • Pankaj Bisane Reply

    You can chanage separator as follows:

    mysql> SELECT *,
    -> GROUP_CONCAT(DISTINCT test_score
    -> ORDER BY test_score DESC SEPARATOR ‘ |’)
    -> FROM table_name

    May 2, 2012 at 1:49 am
  • Ehsan Reply

    Is GROUP_CONCAT() Function in Sql Server too?
    If it has a sample command in sql server please say to me . thanks

    September 1, 2012 at 9:21 am
  • jas Reply

    i have below loop
    for($x=$c;$x!=1;$x–){
    $sql1=”select l2tpCDR.iccid as ic, sum(l2tpCDR.data) as sdata from l2tpCDR inner join sim on sim.ICCID=l2tpCDR.iccid where StatementID=0 and sim.simID='” . $b[$x] . “‘ group by ic”;

    $result1=mysql_query($sql1);
    mysql_error();
    $row1=mysql_fetch_array($result1);
    }

    and i want to tot up all the sum(l2tpCDR.data) as sdata at the end .could you please help me with this?

    November 13, 2012 at 10:05 am
  • Andrey Reply

    Hi, I doubt that GROUP_CONCAT way is faster then usual one. Does anybody did benchmark?

    December 23, 2012 at 5:17 am
  • Neodot Reply

    @Andrey:
    GROUP_CONCAT is rather slower than a regular while( mysql_fetch_array() ) PHP loop. The example here is just for simplicity. But GROUP_CONCAT is really powerful, when it comes to get several records with several “subrecords” without getting too much data (duplicated) from related tables. For example:
    SELECT p.name, p.description, group_concat( pf.filename SEPARATOR ‘:’ ) FROM pages p LEFT JOIN pages_files pf USING( pages_id )
    will get all pages and to every page – all files assigned to it (separated by ‘:’). You can even try something like this:
    SELECT p.name, p.description, group_concat( concat( pf.description, ‘: ‘, pf.filename ) SEPARATOR ‘;’ ) FROM pages p LEFT JOIN pages_files pf USING( pages_id )
    to get same as last time, but with files and their descriptions (file and description separated by :, and different files assigned to the same page separated by 😉 in one query. With a little more work you can get a ready-to-use xml code for this kind of tree-structure, and if not for the group_concat limit – you would be able to construct a single SQL query to extract a ready-to-use XML document containing all pages and their assigned files with all the required attributes – it is up to you to use this grouping function for much more then replacing php while loop, as it’s possibilities are really huge.

    January 17, 2013 at 6:19 am
  • jigar Reply

    thanks,
    Its too simple to understand.

    March 6, 2013 at 1:14 am
  • Marc Reply

    I just wrote up a tiny example to demonstrate the group_concat function. I´m just posting this here since it might be useful for you : http://www.giombetti.com/2013/06/06/mysql-group_concat/

    June 6, 2013 at 2:30 pm
  • Alexis Reply

    Also to change seporator value in case you have commas in your values you can define it with
    group_concat(table.value SEPARATOR ‘|’)

    June 29, 2013 at 9:59 pm
  • Helen Neely Reply

    Thanks for this nice example. Didn’t know you could use group and cat to group together a set of values.
    Nice work.

    January 2, 2014 at 5:46 am

Leave a Reply