QGIS Virtual Layer SQL Query sum if

Question

In QGIS I have a polygon virtual layer (A) with 3 fields:

1. "Codes": Codes of farmers. Each farmer can be duplicated up to 5 times based on next field.
2. "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).
3. "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:

1. 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).

7. 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.

Answer 1

The simplest would be to have one row per farmer and intensity, so that you are not dependent on the intensity values

select intensity, Codes, sum(area_ha) as Total_area_ha,st_union(geometry) as geometry 
from A
group by Codes, intensity

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!

select Codes, 
 sum(area_ha) as Total_area_ha,
 sum(case when intensity=10 then area_ha else 0 end) as area_intensity10,
 sum(case when intensity=20 then area_ha else 0 end) as area_intensity20,
 st_union(geometry) as geometry 
from A
group by Codes

• Javier: It worked great @JGH. Thanks!