When you work with aggregations you sometimes want to concatenate values from multiple rows into a single field. If we look at the table below you might want to group everything by age to see how many people are born in the same year, but also see their name.
Let's start with the query to count the people born in the same here. We group by age and then use the
count aggregation function to see the result
SELECT age, count(*) FROM persons GROUP BY age
This will show you this:
If you want to see the names too, we can use the
SELECT age, count(*), group_concat(name) FROM persons GROUP BY age
There are some options you can specify like
DISTINCT to avoid duplicates,
ORDER BY to sort the data and
SEPARATOR to have a different delimiter.
Here's an example using all available options which will sort the names alphabetical, skip duplicates and separate them by
SELECT age, count(*), group_concat(DISTINCT name ORDER BY name SEPARATOR '; ') FROM persons GROUP BY age
More about that can be found in the MySQL documentation https://dev.mysql.com/doc/en/group-by-functions.html#function_group-concat.