In QGIS I have a polygon virtual layer (A) with 3 fields:
- "Codes": Codes of farmers. Each farmer can be duplicated up to 5 times based on next field.
- "Intensity": Farming intensity. Represents the farming intensity of each farmer on each feature. Ranges from 0 to 90 in 5 steps (0, 20, 40, 80 and 90).
- "Area_ha": Just the area of the polygon.
I want to create a new virtual layer (B) from the previous one that is a "transposed" version. I mean, I want a single feature for each farmer with 5 more columns, each of them with the area of each farming intensity.
It must have the next fields:
- Codes of farmers. Unique, here each farmer can only be once.
2-6. Area of farming intensity X (where X is 0, 20, 40, 80 and 90).
- Total area of farming. The sum of fields 2-6.
I tried with:
select fid, Codes, sum(area_ha) as Total_area_ha,st_union(geometry) as geometry
from A
group by Codes
And I have the new virtual layer with 1 farmer per feature and with the total area, but without the five intensity columns.
I took a look to this post and tried to do it, but I fear is for different things as I don't achieve anything with that:
Wrong result using virtual layer sum with join and group by
Tried also nesting somehow (I am not even a beginner in this) a group by statement, but always returned error.
I come from excel where sumif works easy and great and is difficult for me to understand this SQL commands.
Best Answer
The simplest would be to have one row per farmer and intensity, so that you are not dependent on the intensity values
If you want each intensity as a column, you can set the value being given to
sum
to be either the real value for the corresponding intensity, or 0 for other intensities. You would have to do this for every intensity!