September 4, 2006

GROUP_CONCAT useful GROUP BY extension

Posted by Vadim

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:

CODE:
  1. CREATE TABLE services (
  2. id INT UNSIGNED NOT NULL,
  3. client_id INT UNSIGNED NOT NULL,
  4. KEY (id));
  5. INSERT INTO services
  6. VALUES (1,1),(1,2),(3,5),(3,6),(3,7);
  7.  
  8. SELECT id,client_id FROM services WHERE id = 3;
  9. +----+-----------+
  10. | id | client_id |
  11. +----+-----------+
  12. 3 |         5 |
  13. 3 |         6 |
  14. 3 |         7 |
  15. +----+-----------+
  16.  
  17. SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id;     
  18. +----+-------------------------+
  19. | id | GROUP_CONCAT(client_id) |
  20. +----+-------------------------+
  21. 3 | 5,6,7                   |
  22. +----+-------------------------+

Handling in PHP:
old way:

CODE:
  1. <?php
  2. $res=$mysqli->query("SELECT id,client_id FROM services WHERE id = 3");
  3.  while ($row = $res->fetch_array(MYSQLI_ASSOC)) {
  4.  $result[] = $row['client_id'];
  5. }
  6. $res->free();
  7. ?>

with group_concat:

CODE:
  1. <?php
  2. $res=$mysqli->query("SELECT id,GROUP_CONCAT(client_id) as clients FROM services WHERE id = 3 GROUP BY id");
  3. $row = $res->fetch_array(MYSQLI_ASSOC);
  4. $result = explode(',', $row['clients']); // $row['clients'] contains string 5,6,7
  5. $res->free();
  6. ?>

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:

CODE:
  1. <?php
  2. $res=$mysqli->query("SELECT id,GROUP_CONCAT(client_id) as clients FROM services WHERE id = 3 GROUP BY id");
  3. $row = $res->fetch_array(MYSQLI_ASSOC);
  4. $result = $row['clients']; // $row['clients'] contains string 5,6,7
  5. $res->free();
  6.  
  7. $resclients=$mysqli->query("SELECT id,client_name FROM clients WHERE id = IN ($result)");
  8. // handle $resclients
  9.  
  10. ?>

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

Related posts: :Should MySQL Extend GROUP BY Syntax ?::Wrong GROUP BY makes your queries fragile::Group commit and XA:
 

17 Comments »

  1. 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 …

    Trackback :: September 6, 2006 @ 3:52 am

  2. [...] 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. [...]

    Pingback :: September 7, 2006 @ 12:44 pm

  3. Nice info :-)

    Comment :: October 12, 2006 @ 1:44 am

  4. 4. Vladimir

    there is error in line 4 in example 3

    Comment :: January 27, 2007 @ 7:31 am

  5. Vladimir

    Thank you, fixed.

    Comment :: January 31, 2007 @ 12:28 pm

  6. 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;

    Comment :: June 8, 2007 @ 8:49 am

  7. [...] MySQL Performance Blog [...]

    Pingback :: August 23, 2007 @ 1:10 am

  8. 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

    Comment :: January 24, 2008 @ 9:32 am

  9. 9. Chris

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

    Comment :: January 30, 2008 @ 11:19 am

  10. 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 ‘,’

    Comment :: May 15, 2008 @ 12:28 am

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

    Comment :: June 5, 2008 @ 11:26 pm

  12. There is group_concat_max_len server variable, which by default is 1024

    Comment :: June 6, 2008 @ 3:43 pm

  13. how to extent the limit of group concat > 1024

    Comment :: June 10, 2008 @ 8:52 pm

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

    Comment :: June 11, 2008 @ 10:16 am

  15. 15. DHIRAJ

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

    Comment :: June 12, 2008 @ 12:50 am

  16. 16. Eduardo

    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!

    Comment :: June 30, 2008 @ 7:48 am

  17. Has MySQL rollup, cubes or grouping sets like Oracle?

    Comment :: July 3, 2008 @ 2:30 am

 

Subscribe without commenting


This page was found by: group_concat mysql group_concat group_concat mysql select group_concat mysql group_concat l...