GROUP_CONCAT useful GROUP BY extension

September 2006
M	T	W	T	F	S	S
« Aug		Oct »
	1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

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:

PLAIN TEXT

CODE:

CREATE TABLE services (
id INT UNSIGNED NOT NULL,
client_id INT UNSIGNED NOT NULL,
KEY (id));
INSERT INTO services
VALUES (1,1),(1,2),(3,5),(3,6),(3,7);
SELECT id,client_id FROM services WHERE id = 3;
+----+-----------+
| id | client_id |
+----+-----------+
| 3 | 5 |
| 3 | 6 |
| 3 | 7 |
+----+-----------+
SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id;
+----+-------------------------+
| id | GROUP_CONCAT(client_id) |
+----+-------------------------+
| 3 | 5,6,7 |
+----+-------------------------+

Handling in PHP:
old way:

PLAIN TEXT

CODE:

<?php
$res=$mysqli->query("SELECT id,client_id FROM services WHERE id = 3");
while ($row = $res->fetch_array(MYSQLI_ASSOC)) {
$result[] = $row['client_id'];
}
$res->free();
?>

with group_concat:

PLAIN TEXT

CODE:

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

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:

PLAIN TEXT

CODE:

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

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

Posted by Vadim @ 7:01 am :: tips

Print This Post book mark GROUP_CONCAT useful GROUP BY extension in del.icio.us

del.icio.us :: submit GROUP_CONCAT useful GROUP BY extension to digg.com

digg
Comment RSS

27 Comments »

1. monk.e.boy

Nice info

Comment :: October 12, 2006 @ 1:44 am
2. Vladimir

there is error in line 4 in example 3

Comment :: January 27, 2007 @ 7:31 am
3. Vadim

Vladimir

Thank you, fixed.

Comment :: January 31, 2007 @ 12:28 pm
4. paul carey

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
5. Edison2k3

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

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

Comment :: January 30, 2008 @ 11:19 am
7. Rizwan

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

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
9. Vadim

There is group_concat_max_len server variable, which by default is 1024

Comment :: June 6, 2008 @ 3:43 pm
10. DHIRAJ

how to extent the limit of group concat > 1024

Comment :: June 10, 2008 @ 8:52 pm
11. Vadim

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

Comment :: June 11, 2008 @ 10:16 am
12. DHIRAJ

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

Comment :: June 12, 2008 @ 12:50 am
13. 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
14. Kestas

Has MySQL rollup, cubes or grouping sets like Oracle?

Comment :: July 3, 2008 @ 2:30 am
15. Murz

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;

Comment :: September 24, 2008 @ 10:52 pm
16. JASAN

Murz,

you saved my time. Thanks a lot!.

Comment :: January 6, 2009 @ 6:32 am
17. Mysql - Caggie

@Murz, Jasan

Alternatively you can use

CONVERT(group_concat(myInt) USING utf8)

Comment :: January 27, 2009 @ 6:24 am
18. Martin Zvarik

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??

Comment :: January 27, 2009 @ 6:10 pm
19. Florian 2L

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.

Comment :: April 29, 2009 @ 2:22 am
20. Baron Schwartz

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

Comment :: April 29, 2009 @ 5:32 am
21. Florian 2L

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)

Comment :: May 4, 2009 @ 7:43 am
22. Barbara

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.

Comment :: May 11, 2009 @ 1:27 pm
23. Barbara

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!

Comment :: May 11, 2009 @ 2:53 pm
24. JayaLakshmi

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

Comment :: May 12, 2009 @ 11:42 pm
25. PixelMe

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.

Comment :: May 13, 2009 @ 12:05 pm
26. liki05

hi PixelMe.

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

Comment :: November 11, 2009 @ 12:47 am
27. bhushan

thank bosss “@Murz, Jasan ”

it work for me..

@Murz, Jasan

Alternatively you can use

CONVERT(group_concat(myInt) USING utf8)

Comment :: February 18, 2010 @ 2:40 am

Trackbacks/Pingbacks

VT's Tech Blog
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 ...
Symlinked » Blog Archive » GROUP_CONCAT useful GROUP BY extension
[...] 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. [...]
La fonction group_concat « Développement web
[...] MySQL Performance Blog [...]
The coolest mysql function I didn’t know existed… » Karl Katzke | PHP, Puppies, and other Geekery
[...] group_concat. To use it is to know it, and to know it is to love it. [...]
Función GROUP_CONCAT de MySQL | Otro Blog Más
[...] – Syntax Error y MySQL Performance Blog (mirar también los comentarios) Share this on del.icio.usShare this on [...]

ORDER BY … LIMIT Performance Optimization

Even minor upgrades are not always safe

MySQL Performance Blog

September 4, 2006

GROUP_CONCAT useful GROUP BY extension

27 Comments »

Trackbacks/Pingbacks