[adsense id=”0514458240″ width=”468″ height=”60″]
The GROUP_CONCAT aggregation function returns a non-NULL set of fields within the group. For example, let us imagine that you have a table prize which consists of events and their respective prizes. The table can be seen below:
mysql> select * from prize; +----------+-------+-------+ | event_id | place | money | +----------+-------+-------+ | 101 | 1 | 120 | | 101 | 2 | 60 | | 101 | 3 | 30 | | 102 | 1 | 10 | | 102 | 2 | 5 | | 102 | 3 | 2 | | 103 | 1 | 100 | | 103 | 2 | 60 | | 103 | 3 | 40 | | 401 | 1 | 1000 | +----------+-------+-------+ 10 rows in set (0.00 sec)
Now what we want to do is to get the total prize money per event:
mysql> select event_id, sum(money) from prize group by event_id; +----------+------------+ | event_id | sum(money) | +----------+------------+ | 101 | 210 | | 102 | 17 | | 103 | 200 | | 401 | 1000 | +----------+------------+ 4 rows in set (0.00 sec)
But what if we want to for reference purposes get a comma separate list of the prize money that make up the total amount. Then we would use the GROUP_CONCAT function as follows:
mysql> select event_id, group_concat(money), sum(money) from prize group by event_id; +----------+---------------------+------------+ | event_id | group_concat(money) | sum(money) | +----------+---------------------+------------+ | 101 | 120,60,30 | 210 | | 102 | 2,5,10 | 17 | | 103 | 100,60,40 | 200 | | 401 | 1000 | 1000 | +----------+---------------------+------------+ 4 rows in set (0.00 sec)
Now there are two things we want to do with this set of reference data, the first is we want this ordered with the highest to the lowest values. For event_id 102 this is not the case:
mysql> select event_id, group_concat(money order by money desc), sum(money) from prize group by event_id; +----------+-----------------------------------------+------------+ | event_id | group_concat(money order by money desc) | sum(money) | +----------+-----------------------------------------+------------+ | 101 | 120,60,30 | 210 | | 102 | 10,5,2 | 17 | | 103 | 100,60,40 | 200 | | 401 | 1000 | 1000 | +----------+-----------------------------------------+------------+ 4 rows in set (0.00 sec)
Now the next thing we want to look at is the separator we want to change for a “;”
mysql> select event_id, group_concat(money order by money desc separator ':') as list, sum(money) from prize group by event_id; +----------+-----------+------------+ | event_id | list | sum(money) | +----------+-----------+------------+ | 101 | 120:60:30 | 210 | | 102 | 10:5:2 | 17 | | 103 | 100:60:40 | 200 | | 401 | 1000 | 1000 | +----------+-----------+------------+ 4 rows in set (0.00 sec)