GROUP_CONCAT Function In MySQL

pradeep's Avatar author of GROUP_CONCAT Function In MySQL
This is an article on GROUP_CONCAT Function In MySQL in MySQL.
GROUP_CONCAT function is one MySQL's lesser known functions, which has the ability to ease programmers' work in certain situations. As the name of the function might suggest the function is used to group and output the fields delimited by a delimiting character specified by the user which default to comma, it's grouped by the GROUP BY clause, or limited by the WHERE clause.

Follow the example SQL statements below to get a fair understanding of the usefulness of this function.

Using The GROUP_CONCAT() Function



Here is the data I'll be using in the examples.

Code:
mysql> SELECT * FROM languages;
+----------+---------+
| language | country |
+----------+---------+
| Hindi    | IN      |
| Mandarin | CN      |
| Gujrati  | IN      |
| French   | FR      |
| Tamil    | IN      |
| Tamil    | LK      |
| Urdu     | IN      |
| Urdu     | PK      |
| Sinhala  | LK      |
| Tibetan  | CN      |
+----------+---------+

Using with the WHERE clause

Here we would like to see what are the languages available in a country, say India (IN):

Code:
mysql> SELECT * FROM languages WHERE country = 'IN';
+----------+---------+
| language | country |
+----------+---------+
| Hindi    | IN      |
| Gujrati  | IN      |
| Tamil    | IN      |
| Urdu     | IN      |
+----------+---------+

Now, say we do not want the languages in rows, we want it in CSV (comma separated):

Code:
mysql> SELECT GROUP_CONCAT(language) FROM languages WHERE country = 'IN';
+--------------------------+
| GROUP_CONCAT(language)   |
+--------------------------+
| Hindi,Gujrati,Tamil,Urdu |
+--------------------------+

But, the disadvantage of using WHERE is that you will not be able to output anything other than what GROUP_CONCAT() returns:

Code:
mysql> SELECT country,GROUP_CONCAT(language) FROM languages WHERE country = 'IN';
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

This is perfect when we know the country code, and we would like to fetch the languages for a specific country.

Using with the GROUP BY clause

Here, we'll get the languages available for each country but not comma separated but separated by #:

Code:
mysql> SELECT country,GROUP_CONCAT(language SEPARATOR '#') FROM languages GROUP BY country;
+---------+--------------------------------------+
| country | GROUP_CONCAT(language SEPARATOR '#') |
+---------+--------------------------------------+
| CN      | Tibetan#Mandarin                     |
| FR      | French                               |
| IN      | Urdu#Hindi#Tamil#Gujrati             |
| LK      | Sinhala#Tamil                        |
| PK      | Urdu                                 |
+---------+--------------------------------------+

We may also like to changing the sorting of the values, here's how to do that:

Code:
mysql> SELECT country,GROUP_CONCAT(language ORDER BY language ASC SEPARATOR '#') FROM languages GROUP BY country;
+---------+------------------------------------------------------------+
| country | GROUP_CONCAT(language ORDER BY language ASC SEPARATOR '#') |
+---------+------------------------------------------------------------+
| CN      | Mandarin#Tibetan                                           |
| FR      | French                                                     |
| IN      | Gujrati#Hindi#Tamil#Urdu                                   |
| LK      | Sinhala#Tamil                                              |
| PK      | Urdu                                                       |
+---------+------------------------------------------------------------+

Caveats



It would be good to remember the length of the GROUP_CONCAT() returned string is governed by the group_concat_max_len system variable, so if you need a longer string from GROUP_CONCAT() update the system variable like this:

Code:
SET [SESSION | GLOBAL] group_concat_max_len = val;

Also, GROUP_CONCAT() only uses non-NULL values while grouping fields.

You can also remove duplicate values in the string returned by GROUP_CONCAT() by using DISTINCT.

Code: SQL
SELECT country,GROUP_CONCAT(DISTINCT `language` ORDER BY LANGUAGE ASC SEPARATOR '#') FROM languages GROUP BY country