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.

enter image description here

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:

enter image description here

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?


1 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"


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.