How to use MySQL Aggregate Function GROUP_CONCAT()

Do you think the answer is (390625)²? No. The answer is 1. Notice the first statement 1 = 5.

id	team_id	fname	initials	last
1	1	John		Smith
2	1	Irin	E	Adler
3	1	Michael		Angelo
4	2	Alan		Border
5	2	W	J	Akram

Now you want a report, which will show each team members in a row.

The simple and optimal solution is to use MySQL GROUP_CONCAT() function. MySQL GROUP_CONCAT() aggregate function is a small handy tool to solve this type of problem. This will simplify big efforts to a small piece of work. To see the above output using the power of MySQL GROUP_CONCAT() aggregate function, use the following query:-

mysql > SELECT team_id AS Team, GROUP_CONCAT( CONCAT( last, ', ', fname, ' ', IFNULL('',initials)) ORDER BY last ASC SEPARATOR '; ' ) AS Members FROM team_member GROUP BY team_id ORDER BY team_id ASC;

The output will be like below:-

Team	Members
1	Adler, Irin E; Angelo Michael; Smith, John
2	Border, Alan; Akram, W J

MySQL GROUP_CONCAT() aggregate function is available from version 4.1.

To learn more, have a look at MySQL reference manual. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

You also must know about the MySQL global variable group_concat_max_len. There was a bug regarding this variable which was fixed in a later version 5.0.19. More …

I hope to continue finding the simple and optimal way of problem solving.

Cheers

Ron