QGIS – Virtual Layer SQL Query SUM IF

qgissql

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

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

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
Related Question