Use of various clauses inside GROUP_CONCAT() function
- Distinct: It eliminates the repetition of values from the result.
- Order By: It sorts the values of the group in a specific order and then concatenates them.
- Separator: By default, the values of the group are separated by the (, ) operator. In order to change this separator value, a Separator clause is used followed by a string literal. It is given as Separator ‘str_value’.
Examples:
Let, consider an “Employee” table:
| emp_id | fname | lname | dept_id | strength |
|---|---|---|---|---|
| 1 | mukesh | gupta | 2 | Leadership |
| 3 | neelam | sharma | 3 | Hard-working |
| 1 | mukesh | gupta | 2 | Responsible |
| 2 | devesh | tyagi | 2 | Punctuality |
| 3 | neelam | sharma | 3 | Self-motivated |
| 1 | mukesh | gupta | 2 | Quick-learner |
| 4 | keshav | singhal | 3 | Listening |
| 2 | devesh | tyagi | 2 | Quick-learner |
| 5 | tanya | jain | 1 | Hard-working |
| 4 | keshav | singhal | 3 | Critical thinking |
| 5 | tanya | jain | 1 | Goal-oriented |
Query
1. Using simple GROUP_CONCAT() function –
SELECT emp_id, fname, lname, dept_id,
GROUP_CONCAT ( strength ) as "strengths"
FROM employee
GROUP BY fname;
Output
| emp_id | fname | lname | dept_id | strengths |
|---|---|---|---|---|
| 1 | mukesh | gupta | 2 | Leadership, Responsible, Quick-learner |
| 2 | devesh | tyagi | 2 | Punctuality, Quick-learner |
| 3 | neelam | sharma | 3 | Hard-working, Self-motivated |
| 4 | keshav | singhal | 3 | Listening, Critical thinking |
| 5 | tanya | jain | 1 | Hard-working, Goal-oriented |
2. Using a DISTINCT clause –
Query
SELECT dept_id,
GROUP_CONCAT ( DISTINCT strength) as "employees strengths"
FROM employee
GROUP BY dept_id;
Output
| dept_id | employees strengths |
|---|---|
| 1 | Goal-oriented, Hard-working |
| 2 | Leadership, Punctuality, Quick-learner, Responsible |
| 3 | Critical thinking, Hard-working, Listening, Self-motivated |
3. Use of ORDER BY clause –
Query
SELECT dept_id,
GROUP_CONCAT ( DISTINCT emp_id ORDER BY emp_id SEPARATOR ', ') as "employees ids"
FROM employee
GROUP BY dept_id;
Here, Separator ‘, ‘ will separate the values by a comma (, ) and a whitespace character.
Output
| dept_id | employees ids |
|---|---|
| 1 | 5 |
| 2 | 1, 2 |
| 3 | 3, 4 |
MySQL | Group_CONCAT() Function
The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function that returns a String value if the group contains at least one non-NULL value. Otherwise, it returns NULL.
Syntax:
SELECT col1, col2, ..., colN
GROUP_CONCAT ( [DISTINCT] col_name1
[ORDER BY clause] [SEPARATOR str_val] )
FROM table_name GROUP BY col_name2;
Parameters:
- col1, col2, …colN: These are the column names of the table.
- col_name1: Column of the table whose values are concatenated into a single field for each group.
- table_name: Name of table.
- col_name2: Column of the table according to which grouping is done.