MySQL GROUP_CONCAT() function

Last update on April 27 2024 08:23:33 (UTC/GMT +8 hours)

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
MySQL GROUP_CONCAT() with order by and example
MySQL GROUP_CONCAT() with distinct example
MySQL GROUP_CONCAT() with separator and example

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:


-- This SQL query concatenates category IDs for each publisher.
SELECT pub_id, -- Selects the publisher ID
       GROUP_CONCAT(cate_id) -- Concatenates category IDs for books published by each publisher
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id; -- Groups the results by publisher ID, so that the category IDs are concatenated for each publisher separately

Explanation:

This SQL query retrieves data from the book_mast table.

It concatenates category IDs for each publisher.

The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for category IDs to be concatenated separately for each publisher.

Here's how the process works:

The query selects the publisher ID (pub_id).

It concatenates the category IDs for books published by each publisher using the GROUP_CONCAT(cate_id) function.

The results are grouped by publisher ID using GROUP BY pub_id, ensuring that the category IDs are concatenated separately for each publisher.

Finally, the query returns the publisher ID and the concatenated category IDs for books published by each publisher.

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:


-- This SQL query concatenates distinct category IDs for each publisher and orders the results by the concatenated category IDs in ascending order.
SELECT pub_id, -- Selects the publisher ID
       GROUP_CONCAT(DISTINCT cate_id) -- Concatenates distinct category IDs for books published by each publisher
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id -- Groups the results by publisher ID, so that the distinct category IDs are concatenated for each publisher separately
ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC; -- Orders the results by the concatenated category IDs in ascending order

Explanation:

This SQL query retrieves data from the book_mast table.

It concatenates distinct category IDs for each publisher and orders the results by the concatenated category IDs in ascending order.

The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for distinct category IDs to be concatenated separately for each publisher.

The ORDER BY clause specifies that the results should be ordered by the concatenated category IDs in ascending order.

Here's how the process works:

The query selects the publisher ID (pub_id).
It concatenates the distinct category IDs for books published by each publisher using the GROUP_CONCAT(DISTINCT cate_id) function.

The results are grouped by publisher ID using GROUP BY pub_id, ensuring that the distinct category IDs are concatenated separately for each publisher.

Finally, the query orders the results by the concatenated category IDs in ascending order using ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC.

The query returns the publisher ID and the concatenated distinct category IDs for books published by each publisher, ordered by the concatenated category IDs in ascending order.

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:


-- This SQL query concatenates distinct category IDs for each publisher.
SELECT pub_id, -- Selects the publisher ID
       GROUP_CONCAT(DISTINCT cate_id) -- Concatenates distinct category IDs for books published by each publisher
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id; -- Groups the results by publisher ID, so that the distinct category IDs are concatenated for each publisher separately

Explanation:

This SQL query retrieves data from the book_mast table.

It concatenates distinct category IDs for each publisher.

The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for distinct category IDs to be concatenated separately for each publisher.

Here's how the process works:

The query selects the publisher ID (pub_id).

It concatenates the distinct category IDs for books published by each publisher using the GROUP_CONCAT(DISTINCT cate_id) function.

The results are grouped by publisher ID using GROUP BY pub_id, ensuring that the distinct category IDs are concatenated separately for each publisher.

Finally, the query returns the publisher ID and the concatenated distinct category IDs for books published by each publisher.

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:


-- This SQL query concatenates distinct category IDs for each publisher, ordered alphabetically, with space as separator.
SELECT pub_id, -- Selects the publisher ID
       GROUP_CONCAT(DISTINCT cate_id -- Concatenates distinct category IDs for books published by each publisher
                    ORDER BY cate_id ASC -- Orders the category IDs alphabetically in ascending order
                    SEPARATOR ' ') -- Specifies space as the separator for concatenated category IDs
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id; -- Groups the results by publisher ID, so that the distinct category IDs are concatenated for each publisher separately

Explanation:

This SQL query retrieves data from the book_mast table.

It concatenates distinct category IDs for each publisher, ordered alphabetically, with space as separator.

The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for distinct category IDs to be concatenated separately for each publisher.

The ORDER BY clause orders the category IDs alphabetically in ascending order before concatenating.

The SEPARATOR clause specifies space as the separator for concatenated category IDs.

Here's how the process works:

The query selects the publisher ID (pub_id).

It concatenates the distinct category IDs for books published by each publisher using the GROUP_CONCAT(DISTINCT cate_id ...) function.

The category IDs are ordered alphabetically in ascending order using ORDER BY cate_id ASC before concatenating.

Space is specified as the separator for concatenated category IDs using SEPARATOR ' '.

The results are grouped by publisher ID using GROUP BY pub_id, ensuring that the distinct category IDs are concatenated separately for each publisher.

Finally, the query returns the publisher ID and the concatenated distinct category IDs for books published by each publisher, with category IDs ordered alphabetically and separated by spaces.

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()