Grouping features by attribute with sum function in QGIS

attribute-joinsqgisquery-builder

I want to consolidate my attribute table by grouping it based on several similar features. I cannot share the data due to confidentiality but it goes like this:

1000 data, grouped with same score, name of road, and material. I could use collect geometries tool for this, however I need one of the variables to be summed up, while that tool does not provide that function and only reduce the dimension of my table without allowing me to modify each variable.

I've tried several ways, such as using aggregate processing tool, but it does not work as I expected and a bit too complicated.I also tried to work with query builder by creating virtual layer first, and managed to find the way to sum up the variable I want based on the grouping system I need. However the result only shows the value of that one variable while I need the other variables to be attached to the result too.

I made dummy data to explain it better

enter image description here

So the group I want to make is based on the 3 variables, "Leak Count" variable is to be summed up within the group and for the "Diameter" is to be selected whichever the majority within the group.

Best Answer

Try Aggregate:

This algorithm take a vector or table layer and aggregate features based on a group by expression. Features for which group by expression return the same value are grouped together. It is possible to group all source features together using constant value in group by parameter, example: NULL. It is also possible to group features using multiple fields using Array function, example: Array("Field1", "Field2"). Geometries (if present) are combined into one multipart geometry for each group.

enter image description here

Related Question