When grouping data in mysql you may want to select a subset of your grouping for one output column, but still have that data available for other columns. This use-case means it is not an option to filter the data out in a WHERE clause, but rather we would want to create a conditional GROUP_CONCAT. This is actually possible in MySQL.
For this tutorial we will use the following table:
SELECT * FROM people; +----+-----------+-----+---------+ | id | firstname | age | country | +----+-----------+-----+---------+ | 1 | Bob | 25 | UK | | 2 | Jill | 18 | UK | | 3 | Jen | 36 | UK | | 4 | Jack | 42 | USA | | 5 | John | 19 | Germany | | 6 | Derrick | 32 | Germany | +----+-----------+-----+---------+
If we wanted to create a selection where we group by the country and concatenate all the available surnames we would use the following query:
SELECT country, GROUP_CONCAT(firstname) AS names FROM people GROUP BY country; + | country | names | + | Germany | John,Derrick | | UK | Bob,Jill,Jen | | USA | Jack | +
Conditional GROUP_CONCAT functions
Now if we wanted to include a column where we only concatenate the individuals above the age of 20, we could do that alongside our original query like this:
SELECT country, GROUP_CONCAT(firstname) AS names, GROUP_CONCAT(IF(age > 20, firstname, NULL)) AS older_names FROM people GROUP BY country; + | country | names | older_names | + | Germany | John,Derrick | Derrick | | UK | Bob,Jill,Jen | Bob,Jen | | USA | Jack | Jack | +
As you can see the IF function fits neatly inside the GROUP_CONCAT function and further filters our results for the grouping.
Now we can also see here that if we still wanted to use the full set in other columns we can still do so. By selecting the min age here we can see that we still have the full set as part of our main query.
select country, group_concat(if(age>20, firstname, null)) as older_names, min(age) as min_age from people group by country; + | country | older_names | min_age | + | Germany | Derrick | 19 | | UK | Bob,Jen | 18 | | USA | Jack | 42 | +
If you have any questions or would like to add anything feel free to leave a comment below!
