Dropping duplicate strings when using group_concat in Virtual Layer

Question

When using Virtual Layer to group_concat() a string field, the result would contain duplicates string. For example, here's a polygon layer "city_lot" with "land_use" and "owner" fields.

I want to create a virtual layer that merges lots by owner. So here's the query:

select st_union(geometry),"owner",
    group_concat("land_use") as 'land_use',
    group_concat("fid") as 'fid'
from "city_lot" 
group by "owner"

And here's the result:

Notice that the 3rd record has two "residence" in the "land_use" field. My question is, instead of being "residence,commerce,residence", would that be possible to make the result be "residence,commerce", merging identical string?

Answer 1

To achieve the desired output, use the DISTINCT statement inside of GROUP_CONCAT() function, i.e. group_concat(DISTINCT "land_use") as 'land_use'.

Here is a small example.

Let's assume there is a vector layer with the following attribute table

So, now with this query:

SELECT GROUP_CONCAT("Quality") AS "Quality_Agg", "Quantity"
FROM "random_points_test"
GROUP BY "Quantity"

I will aggregate values from a field "Quality" based on "Quantity" values

To get unique values i.e. drop duplicate values, nest DISTINCT into your query

SELECT GROUP_CONCAT(DISTINCT "Quality") AS "Quality_Agg", "Quantity"
FROM "random_points_test"
GROUP BY "Quantity"

Now the result will look like this

Mind that DISTINCT statement can be used multiple times, e.g.

SELECT "Quantity",
       GROUP_CONCAT(DISTINCT "Quality") AS "Quality_Agg",
       GROUP_CONCAT(DISTINCT "Info") AS "Info_Agg"
FROM "random_points_test"
GROUP BY "Quantity"

---

References:

• MariaDB Server Documentation | GROUP_CONCAT
• MySQL DISTINCT on a GROUP_CONCAT()
• MySQL Tutorial | Introduction to MySQL DISTINCT clause
• MySQL – GROUP_CONCAT unique values