Home > Articles

  • Print
  • + Share This
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
  • + Share This
  • 🔖 Save To Your Account