5

I have a shapefile that contains building footprints with multiple addresses with different functions (with each an usable area). I would like to calculate the sum of the usable area, per building "id", per same "function", as such:

id function area
1 wo 10
1 wi 2
2 wo 50
1 wo 10

↓ calculate sum (area) when same id and same function

id function area
1 wo 20
1 wi 2
2 wo 50

I tried to use the following expression:

sum(expression [,group_by] [,filter])

But the group_by() is only possible once (for instance to group the "id").

How can we calculate the sum of the two features only when these have the same "id" and same function?

2 Answers 2

6

Use a combination of the two fields like concat: "id" || "function" for the group_by() part of the expression:

sum("area", "id" || "function")
1
  • Great, that works thank you!
    – maevad01
    Commented Dec 28, 2020 at 14:18
4

There is also a possibility using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer....

Let's assume there is a point layer called "test" with its attribute table, see image below.

input

With the following query, it is possible to calculate the sum of the features only when these have the same "id" and the same "function", as well as getting rid of duplicates.

SELECT
    SUM(value) AS sumvalue,
    COUNT(*) AS numgrouped,
    "id" || "function" AS grouped,
    geometry AS geom
FROM
    "test"
GROUP BY
    "id", "function"

The output point layer with a new attribute table will look like

result

Last but not the least, keep in mind that:

  • the number of features in the final output will be less (rarely the same) than in the original shapefile
  • grouping values of features with neglecting their geometries is not the same as the grouping values of features with preserving their geometries
1
  • great, that works - thank you!
    – maevad01
    Commented Dec 29, 2020 at 9:10

Your Answer

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

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