- 10.1 Introduction
- 10.2 Grouping on One Column
- 10.3 Grouping on Two or More Columns
- 10.4 Grouping on Expressions
- 10.5 Grouping of Null Values
- 10.6 Grouping with Sorting
- 10.7 General Rules for the GROUP BY Clause
- 10.8 The GROUP_CONCAT Function
- 10.9 Complex Examples with GROUP BY
- 10.10 Grouping with WITH ROLLUP
- 10.11 Answers
This chapter is from the book
This chapter is from the book
This chapter is from the book
10.8 The GROUP_CONCAT Function
A special aggregation function that MySQL supports is the GROUP_CONCAT function. The value of this function is equal to all values of the specified column belonging to a group. These values are placed behind each other, separated by commas, and are presented as one long alphanumeric value.
Example 10.13: For each team, get the team number and list of players who played matches for that team.
SELECT TEAMNO, GROUP_CONCAT(PLAYERNO) FROM MATCHES GROUP BY TEAMNO
The result is:
TEAMNO GROUP_CONCAT(PLAYERNO)
------ ----------------------
1 6,8,57,2,83,44,6,6
2 27,104,112,112,8
The GROUP_CONCAT function can also be used on the column on which the result is grouped.
Example 10.14: For each team, get the team number and for each player, who played matches for that team, get that same team number.
SELECT TEAMNO, GROUP_CONCAT(TEAMNO) FROM MATCHES GROUP BY TEAMNO
The result is:
TEAMNO GROUP_CONCAT(TEAMNO)
------ --------------------
1 1,1,1,1,1,1,1,1
2 2,2,2,2,2
If a select block contains no GROUP BY clause, the GROUP_CONCAT function is processed on all the values of a column.
Example 10.15: Get all the payment numbers.
SELECT GROUP_CONCAT(PAYMENTNO) FROM PENALTIES
The result is:
GROUP_CONCAT(BETALINGSNR) ------------------------- 1,2,3,4,5,6,7,8
The length of the alphanumeric value of a GROUP_CONCAT function is restricted. The system variable GROUP_CONCAT_MAX_LEN indicates the maximum length. This variable has a standard value of 1,024 and can be adjusted with a SET statement.
Example 10.16: Reduce the length of the GROUP_CONCAT function to seven characters and execute the statement of the previous example.
SET @@GROUP_CONCAT_MAX_LEN=7 SELECT TEAMNO, GROUP_CONCAT(TEAMNO) FROM MATCHES GROUP BY TEAMNO
The result is:
TEAMNO GROUP_CONCAT(TEAMNO)
------ --------------------
1 1,1,1,1
2 2,2,2,2