w3resource

MySQL GROUP_CONCAT() function

GROUP_CONCAT() function

MySQL GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group. It is used to concatenate and aggregate values from multiple rows within a specific column into a single string. It's particularly useful for combining and displaying related data in a compact format.

Returns NULL when there are no non-NULL values.

This function is useful in -

  • The primary purpose of the GROUP_CONCAT() function is to concatenate values from multiple rows into a single string.
  • You can use GROUP_CONCAT() to aggregate data based on a certain column or attribute.
  • The GROUP_CONCAT() can use to create a list of tags for each record.
  • When working with hierarchical data structures, you can use GROUP_CONCAT() to show parent-child relationships in a readable format.
  • In applications, you can use GROUP_CONCAT() to display a user's preferences, settings, or selected options in a user-friendly format.
  • GROUP_CONCAT() is useful for displaying data in applications, reports, or user interfaces where a single field needs to show multiple related values.
  • The function allows you to define custom separators (other than commas) and order for concatenated values, giving you flexibility in how data is presented.

Syntax:

GROUP_CONCAT(expr);

Where expr is an expression.

MySQL Version: 8.0

Contents:

Example : MySQL GROUP_CONCAT() function

The following MySQL statement will return a list of comma(,) separated 'cate_id's for each group of 'pub_id' from the book_mast table.

Sample table: book_mast


Code:

SELECT pub_id,GROUP_CONCAT(cate_id)
FROM book_mast
GROUP BY pub_id;

Output:

mysql> SELECT pub_id,GROUP_CONCAT(CATE_ID)
    -> FROM book_mast
    -> GROUP BY pub_id;
+--------+-----------------------+
| pub_id | GROUP_CONCAT(CATE_ID) |
+--------+-----------------------+
| P001   | CA002,CA004           | 
| P002   | CA003,CA003           | 
| P003   | CA001,CA003           | 
| P004   | CA005,CA002           | 
| P005   | CA001,CA004           | 
| P006   | CA005,CA001           | 
| P007   | CA005,CA002           | 
| P008   | CA005,CA004           | 
+--------+-----------------------+
8 rows in set (0.02 sec)

Pictorial Presentation:

example aggregate functions and grouping group_concat

Example: MySQL GROUP_CONCAT() with order by

The following MySQL statement will return unique “cate_id”s , as a list of strings separated by the commas, in ascending order for each group of 'pub_id' from the book_mast table. The order can be changed in descending, using 'DESC' instead of 'ASC' at the end of the select statement.

Sample table: book_mast


Code:

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;

Output:

mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
    -> FROM book_mast 
    -> GROUP BY pub_id
    -> ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;
+--------+--------------------------------+
| pub_id | GROUP_CONCAT(DISTINCT cate_id) |
+--------+--------------------------------+
| P003   | CA001,CA003                    | 
| P005   | CA001,CA004                    | 
| P001   | CA002,CA004                    | 
| P002   | CA003                          | 
| P006   | CA005,CA001                    | 
| P004   | CA005,CA002                    | 
| P007   | CA005,CA002                    | 
| P008   | CA005,CA004                    | 
+--------+--------------------------------+
8 rows in set (0.00 sec)

Example : MySQL GROUP_CONCAT() with distinct

The following MySQL statement will return the unique “cate_id”s, as a list of strings separated by the commas, for each group of 'pub_id' from the book_mast table.

Sample table : book_mast


Code:

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
FROM book_mast
GROUP BY pub_id; 

Output:

mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
    -> FROM book_mast
    -> GROUP BY pub_id;
+--------+--------------------------------+
| pub_id | GROUP_CONCAT(DISTINCT cate_id) |
+--------+--------------------------------+
| P001   | CA002,CA004                    | 
| P002   | CA003                          | 
| P003   | CA001,CA003                    | 
| P004   | CA005,CA002                    | 
| P005   | CA001,CA004                    | 
| P006   | CA005,CA001                    | 
| P007   | CA005,CA002                    | 
| P008   | CA005,CA004                    | 
+--------+--------------------------------+
8 rows in set (0.00 sec)

Example : MySQL GROUP_CONCAT() with separator

The following MySQL statement will return unique “cate_id”s, as a list of strings separated by the specified separator ' '(space) in ascending order for each group of 'pub_id' from the book_mast table. The order can be changed in descending, using 'DESC' option instead of 'ASC' at the end of the select statement.

Sample table : book_mast


Code:

SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id
ORDER BY  cate_id ASC SEPARATOR ' ')
FROM book_mast
GROUP BY pub_id ;

Output:

mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id
    -> ORDER BY  cate_id ASC SEPARATOR ' ')
    -> FROM book_mast
    -> GROUP BY pub_id ;
+--------+--------------------------------------------------------------------+
| pub_id | GROUP_CONCAT(DISTINCT cate_id
ORDER BY  cate_id ASC SEPARATOR ' ') |
+--------+--------------------------------------------------------------------+
| P001   | CA002 CA004                                                        | 
| P002   | CA003                                                              | 
| P003   | CA001 CA003                                                        | 
| P004   | CA002 CA005                                                        | 
| P005   | CA001 CA004                                                        | 
| P006   | CA001 CA005                                                        | 
| P007   | CA002 CA005                                                        | 
| P008   | CA004 CA005                                                        | 
+--------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)

Previous: COUNT(DISTINCT)
Next: MySQL Aggregate Functions and Grouping - Max()



Follow us on Facebook and Twitter for latest update.