GROUP_CONCAT Function In MySQL

Discussion in 'MySQL' started by pradeep, Jun 5, 2012.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    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:
    SELECT country,GROUP_CONCAT(DISTINCT `language` ORDER BY language ASC SEPARATOR '#') FROM languages GROUP BY country
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice