SQL

Concatenate multiple rows into a single field


Problem

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.

id title age
1 Bob 24
2 Joe 48
3 Jeff 48


Solution

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:

age count(*)
24 1
48 2

If you want to see the names too, we can use the GROUP_CONCAT function:

SELECT age, count(*), group_concat(name)
FROM persons
GROUP BY age
age count(*) group_concat(name)
24 1 Bob
48 2 Joe,Jeff

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.

Comments




Please sign-in to post a comment