Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   GROUP_CONCAT Function In MySQL (http://www.go4expert.com/articles/groupconcat-function-mysql-t28525/)

pradeep 5Jun2012 17:43

GROUP_CONCAT Function 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



All times are GMT +5.5. The time now is 09:21.