How the GROUP_CONCAT() function works in Mariadb?

The GROUP_CONCAT() function is an aggregate function that returns a concatenated string of the non-null values in a group.

Posted on

The GROUP_CONCAT() function is an aggregate function that returns a concatenated string of the non-null values in a group. It can be used to combine multiple values from a column or an expression into a single string. The GROUP_CONCAT() function is compatible with the SQL standard and supports various data types, such as INT, DECIMAL, VARCHAR, DATE, TIME, and DATETIME.

Syntax

The syntax of the GROUP_CONCAT() function is as follows:

GROUP_CONCAT([DISTINCT] expression [ORDER BY {column | expression | position} [ASC | DESC] [, ...]] [SEPARATOR separator])

where expression is the column or the expression to be concatenated, DISTINCT is an optional keyword that removes duplicate values, ORDER BY is an optional clause that specifies the order of the values, column, expression, or position are the columns, expressions, or positions to order by, ASC or DESC are the optional keywords that specify the ascending or descending order, SEPARATOR is an optional keyword that specifies the separator between the values, and separator is the separator string. The function returns a string that contains the concatenated values. If the group is empty or contains only null values, the function returns NULL. If the result exceeds the maximum length of a string, the function returns a truncated string.

Examples

Example 1: Concatenate values from a column

The following example uses the GROUP_CONCAT() function to concatenate the values from the name column of the students table.

SELECT GROUP_CONCAT(name) AS names
FROM students;

The function returns a string that contains the names of all the students in the table, separated by commas. The output is:

+--------------------------+
| names                    |
+--------------------------+
| Alice, Bob, Charlie, Dan |
+--------------------------+

The concatenated string of the names is ‘Alice, Bob, Charlie, Dan’.

Example 2: Concatenate distinct values from a column

The following example uses the GROUP_CONCAT() function with the DISTINCT keyword to concatenate the distinct values from the course column of the enrollments table.

SELECT GROUP_CONCAT(DISTINCT course) AS courses
FROM enrollments;

The function returns a string that contains the distinct courses that the students have enrolled in, separated by commas. The output is:

+---------------------+
| courses             |
+---------------------+
| Math, Physics, CS   |
+---------------------+

The concatenated string of the distinct courses is ‘Math, Physics, CS’.

Example 3: Concatenate values from a column with a custom order and separator

The following example uses the GROUP_CONCAT() function with the ORDER BY clause and the SEPARATOR keyword to concatenate the values from the score column of the exams table with a custom order and separator.

SELECT GROUP_CONCAT(score ORDER BY score DESC SEPARATOR ' - ') AS scores
FROM exams;

The function returns a string that contains the scores of the exams, ordered by descending order, and separated by dashes. The output is:

+---------------------+
| scores              |
+---------------------+
| 95 - 90 - 85 - 80   |
+---------------------+

The concatenated string of the scores is ‘95 - 90 - 85 - 80’.

Some of the functions that are related to the GROUP_CONCAT() function are:

  • CONCAT(): This function returns a concatenated string of two or more values. It has a different syntax and behavior from the GROUP_CONCAT() function. For example, CONCAT('a', 'b', 'c') returns ‘abc’, while GROUP_CONCAT('a', 'b', 'c') returns an error.
  • CONCAT_WS(): This function returns a concatenated string of two or more values with a specified separator. It has a different syntax and behavior from the GROUP_CONCAT() function. For example, CONCAT_WS('-', 'a', 'b', 'c') returns ‘a-b-c’, while GROUP_CONCAT('a', 'b', 'c' SEPARATOR '-') returns an error.

Conclusion

The GROUP_CONCAT() function is a useful function to concatenate the non-null values in a group into a single string. It supports various data types and follows the SQL standard. It can be used to combine multiple values from a column or an expression with optional modifiers, such as DISTINCT, ORDER BY, and SEPARATOR. It has some related functions that have similar or different functionalities. The GROUP_CONCAT() function is a powerful tool for data analysis and manipulation in Mariadb.