QGIS – Using Aggregate Expression Function with GroupBy Argument

Question

I have a Layer "SplitNEW" which is grouped by the "group"-Column as follow:
0-RED , 1-GREEN , 3-BLUE.

The 3 Lines where previous split. Each Row in attribute table stands for an line segment as selected ( just for visualization purposes ). Each segment has a value "sum_in_kW" wich was aggregated from another Layer. I assigned for each segment in his group an segment id ("seg_id") and it iterate it ( order from top to bottom of the line – direction of the line ).

What i would like to do is sum up the column "sum_in_kw" for the current segment and and the segment before the current segment ( filter: per group ).

I allready have a solutin but this involves "Split vector Layer" by the column "group" resulting in 3 Layers ( in reality i deal with much more groups). For each Layer by BatchProcess I would add an column "sum_segement" with the following aggregate:

aggregate(@layer_name, 'sum', "sum_in_kW", "seg_id"<=attribute(@parent, 'seg_id'))

After that I can merge all the Layers back.

My question: Is there any way to use an aggregate or some other function as needed to sum up the segments in the right order of "seg_id" and adding somehow a filter to perform this per "group".

Answer 1

You can create a virtual layer that will compute this field. You can then export it to a new permanent layer if you wish to persist the result.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Replace myLineLayer and the field names with the real names.

select *, sum(dist) over (partition by grp order by id asc) as cumul_dist
from myLineLayer