How to use MySQL Aggregate Function GROUP_CONCAT()

Few days ago, I got a mail from one of my friend. The mail was asking a question like below:-

Answer the following question within 10 sec and without the help of a calculator.

If 1 = 5, 2 = 25, 3 = 625, 4 = 390625, then 5 = ?

Answer is hidden here

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

I asked this question to some other friends. They also replied the wrong answer. The answer is very simple. But sometimes we make complex and wrong solutions of simple problems. This happens due to lack of proper understanding the 'system'.

Here is similar problem, which occurs frequently in our day to day programming.

Let you have team_member table data like below:-

Read the rest of this article...

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